0

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
Community
  • 1
  • 1

2 Answers2

2

incase anyone sees this and needs an answer i have figured it out

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
    signature = .Getinspector
    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
  • This serendipitous "rediscovery" of the `.GetInspector` trick explained. [How to add default signature in Outlook](https://stackoverflow.com/a/72719341/1571407). – niton Jun 24 '22 at 15:01
0

To get the signature added to the mail item you need to call the GetInspector method before getting the HTMLBody property value.

With objMail

    .Getinspector
    signature = .Htmlbody
Eugene Astafiev
  • 47,483
  • 3
  • 24
  • 45