CODEWITHSUNDEEP
emailvbaoutlookattachment
Alexei
Alexei

Reputation: 1

Attachments becoming embedded

I have an Excel macro used to send emails with attachments that are Excel workbooks. Sometimes, instead of the file being attached, it becomes an embedded object. This embedded object is openable by users 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 for those outside of the company the email appears to have no attachment at all.

This appears to happen randomly, and only on some computers. So if the list has 15 email lists and attachments, it happens 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. 

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

Upvotes: 0

Views: 2361

Answers (1)

Ron Savage
Ron Savage

Reputation: 11079

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.

Upvotes: 1

Related Questions