Word VBA code to attach current document to an email template and insert data from a userform into body

First time poster here.

I've been struggling with this for days now and would really appreciate some help with it.

I have a limited knowledge of VBA and have cobbled together the macro below, which attaches the current Word doc to an email template, populates the Subject field and inserts text at two bookmarks in the body of the email. This works ok, but it means that the user has to fill in two separate InputBox text fields that pop up one after the other.

What I really want to do is replace these with a single Userform with two text fields for Client Name and Client Reference. I just can't seem to find the code to enter the name and reference number at the the bookmarks.

Any help would be much appreciated.

Thanks

Neil

Sub Email_SO_mandate()

Dim bStarted As Boolean
Dim oOutlookApp As Outlook.Application
Dim oitem As Outlook.MailItem

On Error Resume Next

ActiveDocument.SaveAs FileName:="H:\Email templates\Standing order mandate"

Set oOutlookApp = GetObject(, "Outlook.Application")

If Err <> 0 Then
    Set oOutlookApp = CreateObject("Outlook.Application")
    bStarted = True

End If

Set oitem = oOutlookApp.CreateItemFromTemplate("H:\Email templates\SO mandate email template.oft")

With oitem
    .To = ""
    .Subject = "Standing Order Mandate"
    'Add the document as an attachment, you can use the .displayname property
    'to set the description that's used in the message
    .Attachments.Add Source:=ActiveDocument.FullName, Type:=olByValue, _
      DisplayName:="Document as attachment"

    strMgrName = InputBox("Client's Name (first name, or title & surname)")
    oitem.HTMLBody = Replace(oitem.HTMLBody, "client_name", strMgrName)

    strMgrName = InputBox("Client's Reference Nunber")
    oitem.HTMLBody = Replace(oitem.HTMLBody, "ref_no1", strMgrName)
    oitem.HTMLBody = Replace(oitem.HTMLBody, "ref_no2", strMgrName)

    oitem.Display
    .Display

End With

Set oitem = Nothing
Set oOutlookApp = Nothing

End Sub

This thread is locked. You can vote as helpful, but you cannot reply or subscribe to this thread.

Answer

Graham Mayor

By 'bookmarks' I take it that you mean text strings in the Outlook template? This would be fairly straightforward. Create a userform with two textboxes and a command button (you can add more fields if you wish), http://www.gmayor.com/Userform.htm

Associate the following code with the command button.

Private Sub CommandButton1_Click()
Me.Hide
Me.Tag = 1
End Sub

If you want a Cancel button set its Tag value to 0

Then modify your code as follows

Sub Email_SO_mandate()
'Uses late binding so no need to set a reference to Outlook

Dim bStarted As Boolean
Dim oOutlookApp As Object
Dim oitem As Object
Dim oDoc as Document

    With UserForm1
        .Show
        If NOT .Tag = 1 Then Exit Sub
       Set oDoc = ActiveDocument
        On Error Resume Next
        oDoc.SaveAs Filename:="H:\Email templates\Standing order mandate.docx"
        Set oOutlookApp = GetObject(, "Outlook.Application")
        If Err <> 0 Then
            Set oOutlookApp = CreateObject("Outlook.Application")
            bStarted = True
        End If
        Set oitem = oOutlookApp.CreateItemFromTemplate("H:\Email templates\SO mandate email template.oft")
        With oitem
            .to = ""
            .Subject = "Standing Order Mandate"
            'Add the document as an attachment, you can use the .displayname property
            'to set the description that's used in the message
            .Attachments.Add oDoc.FullName
            .HTMLBody = Replace(.HTMLBody, "client_name", UserForm1.TextBox1.Text)
            .HTMLBody = Replace(.HTMLBody, "ref_no1", UserForm1.TextBox2.Text)
            .HTMLBody = Replace(.HTMLBody, "ref_no2", UserForm1.TextBox2.Text)
            .Display
        End With
    End With
    Unload UserForm1
    Set oitem = Nothing
    Set oOutlookApp = Nothing
    Set oDoc = Nothing
End Sub

Graham Mayor (Microsoft Word MVP 2002-2019)
For more Word tips and downloads visit my web site
https://www.gmayor.com/Word_pages.htm

Contribute to the Microsoft 365 and Office forum!

Word Forum Top Contributors:

Word VBA code to attach current document to an email template and insert data from a userform into body

Replies (4)

Question Info

Word VBA code to attach current document to an email template and insert data from a userform into body

Replies (4) 

Question Info

Replies (4)