Not update screen while sending emails through vba-CodePudding

i have a spreadsheet with a list of emails (for this example imagine the emails vary from Cell A1 to Cell A100). i need to send a slightly different email to each email address in this range. i have my script complete but in order to keep the signature i must display the email and this makes the macro take a lot of time to run. i have seen other questions that use .Getinspector the issue i have had with this is once it sends the First email and sets it to nothing for some reason.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

CodePudding user response：

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

CodePudding user response：

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