Auto attach active file and specific template in body of outlook

Jeet_Dhillon

New Member
Joined
Jul 13, 2019
Messages
19
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
 

Some videos you may like

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.

GWteB

Well-known Member
Joined
Sep 10, 2010
Messages
1,295
Office Version
  1. 2013
Platform
  1. Windows
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.".

VBA Code:
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

New Member
Joined
Jul 13, 2019
Messages
19
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.".

VBA Code:
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 :

1604763358758.png


Thank you
 

GWteB

Well-known Member
Joined
Sep 10, 2010
Messages
1,295
Office Version
  1. 2013
Platform
  1. Windows
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?
 

Watch MrExcel Video

Forum statistics

Threads
1,127,301
Messages
5,623,855
Members
415,995
Latest member
SergioCM92

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top