Log In
Register
Home
December 4, 2021
Offline
I have a fully functional VBA code. This code send mass emails from Excel (email addresses in Column B). Thanks to the wonderful people in this forum, is is working great!!! The data sheet will consist of a Master List of email names. However, because of the help I received here, this template will now placed on SharePoint. As a result, I need to remove the OFT path/filename from the VBA that is currently hardcoded, and replace it with a cell reference. I tried a few different things, but nothing worked properly.
The sheet reference can be via sheet "codename" or sheet name. I prefer codename as you never know if someone will change the sheet name and then the macro code breaks.
Sheet name: "Main"
codename: sheet1
cell reference: E25
- path in cell E25: C:\Users\barnes22\AppData\Roaming\Microsoft\Templates\Mass Email\Change Notification.oft
Option Explicit
Sub Email_ChangeNotification()
Dim OutApp As Object
Dim OutMail As Object
Dim EmailTo As String
Dim LastRw As Long
Dim i As Integer
Dim SentOnBehalfOfName As String
' Set Outlook object.
Set OutApp = CreateObject("Outlook.Application")
LastRw = Range("B" & Rows.Count).End(xlUp).Row
' Loop of 500 email addresses
For i = 2 To LastRw Step 500
' Sheet where email List is located
EmailTo = Join(Application.Transpose(Sheet7.Range("B" & i & ":B" & WorksheetFunction.Min(i + 499, LastRw)).Value), ";")
' Change to path of OFT Template
' Sheet7 = ChangeNotification
Set OutMail = OutApp.CreateItemFromTemplate("C:\Users\barnes22\AppData\Roaming\Microsoft\Templates\Mass Email\Change Notification.oft")
With OutMail
.SentOnBehalfOfName = "shared@mycompany.com" ' Shared Email
.To = EmailTo ' Column B of Sheet # listed above
.CC = ""
.BCC = ""
.Display
'.send
End With
Next i
On Error GoTo 0
Set OutMail = Nothing
Set OutApp = Nothing
End Sub
See Answer
Trusted Members
Moderators
November 1, 2018
Offline
Set OutMail = OutApp.CreateItemFromTemplate(sheet1.range("E25").value)
should do it.
Answers Post
December 4, 2021
Offline
@Velouria,
OMG, where has time gone? Projects dumped on me left and right, and now I am down to 21 active projects!!! I tried your code, and it works great. Thanks as usual, you are always a HUGE help to me, and it is greatly appreciated!!!!!
The following users say thank you to Sherry Fox for this useful post:
Velouria
1 Guest(s)
