Using Excel VBA to send emails. Problem with attachments becoming embedded by accident.

Question

Hi, I am having an issue with an Excel macro I wrote that is used by several users within my company. It is used to send numerous emails daily with attachments that are also Excel workbooks. The issue is that sometimes, instead of the file simply being attached as it should be, it becomes an embedded object. This embedded object is openable by users on the email within the company (after clicking through the "YOu are about to activate an embedded object that may contain viruses or be otherwise harmful to your computer. It is important to be certain that it is from a trustworthy source. Do you want to continue?"), but those outside of the company do not see it at all. The email appears to have no attachment at all.

Curiously, this appears to happen randomly, and only on some computers. So if the list has 15 email lists and attachments, it seems to happen randomly to anywhere between 0 and 15 of the emails. To be clear, my objective is to send emails with regular attachments. Running Excel 2003, Outlook 2003, and Windows XP.

See code below. Please help!

Sub Email()  
Dim P As String  
Dim N As String  
Dim M As String  
Dim Subject As String  
Dim Addresses As String  
Dim olApp As Outlook.Application  
Dim olNewMail As Outlook.MailItem  

Application.DisplayAlerts = False  

M = ActiveWorkbook.Name  

For c = 2 To 64000  
    If Range("B" & c) = "" Then Exit For  
    If UCase(Range("E" & c)) = "Y" Then  
        Workbooks(M).Sheets("Main").Activate  
        Subject = Range("A" & c)  
        Addresses = Range("B" & c)  
        P = Range("C" & c)  
        N = Range("D" & c)  
        If Right(P, 1) <> "\" Then P = P & "\"  
        If Right(N, 4) <> ".xls" Then N = N & ".xls"  
        Set olApp = New Outlook.Application  
        Set olNewMail = olApp.CreateItem(olMailItem)  
        With olNewMail  
        .Display  
        .Recipients.Add Addresses  
        Application.Wait (Now + TimeValue("0:00:01"))  
        SendKeys ("{TAB}")  
        .Subject = Subject  
        .Attachments.Add P + N  
        .Send  
        End With  
        Set olNewMail = Nothing  
        Set olApp = Nothing  

    End If  
Next c  

Range("E2:E65536").ClearContents  
Application.DisplayAlerts = True  

End Sub  

Answer 1

You probably just need to explicitly set the format of the message using the olMailItem.BodyFormat property: http://msdn.microsoft.com/en-us/library/aa211430(v=office.11).aspx

It sounds like it is being displayed as a Rich Format message (with the attachment as an embedded object) rather than an HTML format e-mail.