Auto attach active file and specific template in body of outlook

[Jeet_Dhillon]

Hello Friends,

I am trying to prepare a code to attach an active file and insert a specific template in the email body.
So far, my progress is mentioned below, and I need help with attaching a file with a template that is saved in the Shared folder on the network .

Sub SendWorkBook()

Dim OutlookApp As Object
Dim OutlookMail As Object
Set OutlookApp = CreateObject("Outlook.Application")
Set OutlookMail = OutlookApp.CreateItem(0)

'Set OutlookMail = OutlookApp.CreateItemFromTemplate("P:\Garage - Eglinton\Forepersons Report\Outlook Template\End of Shift Report.oft")'

On Error Resume Next

'With CreateObject("Outlook.Application").CreateItemFromTemplate("P:\Garage - Eglinton\Forepersons Report\Outlook Template\End of Shift Report.oft")'

With OutlookMail
.To = ""
.CC = ""
.BCC = ""
.Subject = "testing"
.Body = "Testing ."
.Attachments.Add Application.ActiveWorkbook.FullName
.Display
End With
Set OutlookMail = Nothing
Set OutlookApp = Nothing
End Sub

Thank you

 

[GWteB]

You are on the right track, however, the purpose of a template is to compose an email message in a specific form once and then use the template several times.
Currently, not only the subject but also the body is overwritten, so the content of your template is replaced by something else, namely by "Testing.".

Sub SendWorkBook()

    Dim OutlookApp As Object
    Dim OutlookMail As Object
    
    Set OutlookApp = CreateObject("Outlook.Application")
    Set OutlookMail = OutlookApp.CreateItemFromTemplate("P:\Garage - Eglinton\Forepersons Report\Outlook Template\End of Shift Report.oft")
    With OutlookMail
        .To = ""
        .CC = ""
        .BCC = ""
        .Subject = "a random subject"
    '    .Body = "Testing ."              ' <<<<<< this line can be dropped
        .Attachments.Add Application.ActiveWorkbook.FullName
        .Display
    End With
    Set OutlookMail = Nothing
    Set OutlookApp = Nothing
End Sub

 

[Jeet_Dhillon]

You are on the right track, however, the purpose of a template is to compose an email message in a specific form once and then use the template several times.
Currently, not only the subject but also the body is overwritten, so the content of your template is replaced by something else, namely by "Testing.".

Sub SendWorkBook()

    Dim OutlookApp As Object
    Dim OutlookMail As Object
   
    Set OutlookApp = CreateObject("Outlook.Application")
    Set OutlookMail = OutlookApp.CreateItemFromTemplate("P:\Garage - Eglinton\Forepersons Report\Outlook Template\End of Shift Report.oft")
    With OutlookMail
        .To = ""
        .CC = ""
        .BCC = ""
        .Subject = "a random subject"
    '    .Body = "Testing ."              ' <<<<<< this line can be dropped
        .Attachments.Add Application.ActiveWorkbook.FullName
        .Display
    End With
    Set OutlookMail = Nothing
    Set OutlookApp = Nothing
End Sub

Hi,
Thanks for replying, after trying this version I am getting this error :

Thank you

 

[GWteB]

I see ...
Replacing the CreateItemFromTemplate argument with a valid OFT template on my system (F:\MrExcel\Test\tmp.oft) it works for me.
On which line are you getting this error?