I have a spreadsheet with a list of email addresses (for this example imagine the email addresses are in Cell A1 to Cell A100).
I need to send a different email to each email address in this range. To keep the signature I must display the email and this makes the macro take a lot of time to run.
I have seen questions that use .GetInspector
. The issue I have with this is once it sends the first email and sets it to nothing .GetInspector
interferes with opening the mail item for the second email.
Is there any way to work around .Display
with the code I have?
Sub Sendemails()
Dim objOutlook As Outlook.Application
Dim objMail As Outlook.MailItem
Dim signature As Variant
Dim rngemailaddress, Emailaddress As Range
Dim numberofrows As Long
numberofrows = Sheet1.Range("A100000").End(xlUp).Row
Set rngemailaddress = Range("A1:A" & numberofrows)
For Each Emailaddress In rngemailaddress.Cells
Set objOutlook = Outlook.Application
Set objMail = objOutlook.CreateItem(olMailItem)
With objMail
'.Display is so i can save the signature
.Display
signature = .Htmlbody
'instead of the line above i also tried "Signature = .GetInspector"
.to = Emailaddress.Value
.Subject = "Test email to not Display"
.Htmlbody = "Input variable information here" & "<br><br>" & signature
.Close olsave
End With
Set objMail = Nothing
Next
End Sub