VBA to send emails using Outlook .oft template

ssh99

New Member
Joined
Oct 25, 2020
Messages
9
Office Version
  1. 2010
Platform
  1. Windows
  2. MacOS
I have a script I use to automate sending emails on Outlook. It works fine but I now want it to send the emails using a template. I have added the 'NewMail as Outlook.Object' line to the code to try and do this but get the following error: Run-Time Error 424: Object Required. I have checked the file mapping for the template to be used and it is correct. Can anyone help resolve this?

The VBA code is:

VBA Code:
Sub Send_Emails()



Dim OutApp As Object

Dim OutMail As Object

Dim OutAccount As Outlook.Account

Dim NewMail As Outlook.MailItem



For i = 2 To Sheet1.Cells(Rows.Count, 2).End(xlUp).Row

Set OutApp = CreateObject("Outlook.Application")

Set OutMail = OutApp.CreateItem(0)

Set OutAccount = OutApp.Session.Accounts.Item(1)

Set NewMail = obApp.CreateItemFromTemplate("G:\Shared\test")



On Error Resume Next

With NewMail

.To = Cells(i, 2).Value

.Attachments.Add Cells(i, 3).Value

.Importance = olImportanceHigh

.OriginatorDeliveryReportRequested = True

.Send

End With

On Error GoTo 0



Set OutMail = Nothing

Set OutApp = Nothing

Next



End Sub
 
Last edited by a moderator:

Some videos you may like

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.

NdNoviceHlp

Well-known Member
Joined
Nov 9, 2002
Messages
2,673
Code:
"G:\Shared\test.dot"
or whatever... U are missing the file extension. HTH. Dave
 

ssh99

New Member
Joined
Oct 25, 2020
Messages
9
Office Version
  1. 2010
Platform
  1. Windows
  2. MacOS
Code:
"G:\Shared\test.dot"
or whatever... U are missing the file extension. HTH. Dave
I updated the code so it now says "G:\Shared\test.oft". But the same error still comes up. Any idea what's wrong?
 

NdNoviceHlp

Well-known Member
Joined
Nov 9, 2002
Messages
2,673
Sorry if the file exists, I'm not sure what is wrong. Google seems to suggest trying opening the file manually and/or renaming the file.... or adding code to let the user pick the file instead of hard coding the file address and then use a variable derived from the file picker. Good luck. Dave
 

GWteB

Well-known Member
Joined
Sep 10, 2010
Messages
1,295
Office Version
  1. 2013
Platform
  1. Windows

ADVERTISEMENT

To get it work change this ...
Rich (BB code):
Set NewMail = obApp.CreateItemFromTemplate("G:\Shared\test")

into this ...
Rich (BB code):
Set NewMail = OutApp.CreateItemFromTemplate("G:\Shared\test.oft")
 
Solution

ssh99

New Member
Joined
Oct 25, 2020
Messages
9
Office Version
  1. 2010
Platform
  1. Windows
  2. MacOS
To get it work change this ...
Rich (BB code):
Set NewMail = obApp.CreateItemFromTemplate("G:\Shared\test")

into this ...
Rich (BB code):
Set NewMail = OutApp.CreateItemFromTemplate("G:\Shared\test.oft")
That's worked perfectly. Thank you!
 

GWteB

Well-known Member
Joined
Sep 10, 2010
Messages
1,295
Office Version
  1. 2013
Platform
  1. Windows
Glad it's sorted and thanks for letting me know.
 

Watch MrExcel Video

Forum statistics

Threads
1,119,076
Messages
5,575,988
Members
412,693
Latest member
SJC2020
Top