CODEWITHSUNDEEP
excelexcel-2010outlook-2010vba
Maldred
Maldred

Reputation: 1104

Edit Outlook email from Excel VBA

I've got the below code to successfully use a pre-made Outlook template that is saved locally on my machine (fileName) and attach the Active Excel document into it, however there is some additional text that I'd like to add to the email template to save me the time to copy and paste it over. Is there anyway to add ADDITIONAL body text to the pre-made email template or if I can have my VBA code read the body text and then I can add to it by storing it in a temporary variable? It's a saved .msg file

Public Function GenerateEmail(sendTo As String, _
    sendCC As String, sendBCC As String, _
    subjectText As String, fileName As String)

    Application.ScreenUpdating = False

    Dim OutApp As Object
    Dim OutMail As Object

    Set OutApp = CreateObject("Outlook.Application")
    Set OutMail = OutApp.CreateItemFromTemplate(fileName)

    With OutMail
        .sendTo = sendToText
        .CC = sendCCText
        .BCC = sendBCCText
        .Subject = subjectText
        .Attachments.Add (Application.ActiveWorkbook.FullName)
        .Display
    End With

    On Error GoTo 0

    Set OutMail = Nothing
    Set OutApp = Nothing

    With Application
        .ScreenUpdating = True
        .EnableEvents = True
    End With

End Function

Upvotes: 2

Views: 5512

Answers (2)

Jeremy
Jeremy

Reputation: 1337

You have to save down a template - you just draft a template (I typically use markers to substitute certain part of the body, depending on the recipient - like "Dear %Recipient%") and then "Save as" a .oft file. then run you code to sent the mails. I'd also use .HTMLbody to maintain the fomatting of the template so you'd put in 

With OutMail
        .sendTo = sendToText
        .CC = sendCCText
        .BCC = sendBCCText
        .Subject = subjectText
        .HTMLbody= WorksheetFunction.Substitute(OutMail.HTMLbody, "%Recipient%", [Recipiants name here (this could be a stored string)])
        .Attachments.Add (Application.ActiveWorkbook.FullName)
        .Display
End With

Upvotes: 1

Kyoujin
Kyoujin

Reputation: 324

Just add a .body to the email text such as below

 With OutMail
        .sendTo = sendToText
        .CC = sendCCText
        .BCC = sendBCCText
        .Subject = subjectText
        .body = "Add your Text here"
        .Attachments.Add (Application.ActiveWorkbook.FullName)
        .Display
End With

Upvotes: 0

Related Questions