Using VBA to attach a file in an outlook email

2
votes

I've created a subroutine that grabs all the relevant details and attachments to send out automated emails for me. Here is the code I have:

Sub Mail_workbook_Outlook_1()
    Dim OutApp As Outlook.Application
    Dim OutMail As Outlook.MailItem
    Dim To1 As String, CC1 As String, BCC1 As String, Title1 As String, Body1 As String, Att1 As String


' Create "Other Distribution - In Email" Emails

    Set OutApp = CreateObject("Outlook.Application")
    Set OutMail = OutApp.CreateItem(olMailItem)
To1 = Cells(8, 2).Value
CC1 = Cells(8, 3).Value
BCC1 = Cells(8, 4).Value
Title1 = Cells(8, 5).Value
Body1 = Cells(8, 6).Value
Att1 = Cells(8, 7).Value

    On Error Resume Next
    With OutMail
        ' BodyFormat command makes the email a rich format message allowing us to place attachments within the body of the email instead of in the attachment header
        .BodyFormat = olFormatRichText
        .To = To1
        .CC = CC1
        .BCC = BCC1
        .Subject = Title1
        .Body = Body1
        .Attachments.Add Att1, , 10
        .Display
    End With
    On Error GoTo 0

    Set OutMail = Nothing
    Set OutApp = Nothing
End Sub

This works fine and inserts my attachment at the end of my email body. The issue is this specific line:

.Attachments.Add Att1, , 10

In this code the "10" is supposed to represent the position of the attachment. I.E. this is supposed to mean that after the first 9 characters in my "Body1" variable, instead of the 10th character the attachment will be placed here. See here: https://msdn.microsoft.com/en-us/library/office/ff869553.aspx

However, for some reason no matter what number I put in the position option it always just puts my attachment at the very end of the email. I need the attachment to be in the middle of several paragraphs so this is causing an issue. Any idea what is causing this?

I should mention I have selected the Microsoft Outlook Object Library from Tools>References.

Any help is greatly appreciated!

2
vbaemailoutlook

2 Answers

1
votes

So I found out that this is a bug in Outlook 2008/2010 for which there does not seem to be a fix :(

http://argurosblog.blogspot.com/2011/11/how-to-create-task-or-appointment-using.html

0
votes

Change the content of the body with:

    Body1 = "This is the body of the mail, line 1" & vbcrlf 
    Body1 = Body1 &  "This is the second line of text, line 2" & vbcrlf 
    Body1 = Body1 & "This is the last line of text, line 3."

and run your code.

As you can see the attachment is not placed after the 10.th character, but after the first vbcrlf found after the 10.th character.

If you try with .Attachments.Add Att1, , 50 (in the middle of the second line), it will be placed between line 2 and line 3.

If you delete all the vbcrlfs characters in your body, it will placed at the end of the body, and that is probably what happens to you.

Parse the content of your body and insert vbcrlf ('hard returns') characters where needed.

Hope this helps.