CODEWITHSUNDEEP
excelvbaemailexcel-2007
whispersan
whispersan

Reputation: 1039

Excel Email Hanging Outlook until Sent - Error Handling?

I'm sending an Excel workbook using a macro as below. I use a blank email address so that it brings up the email in Outlook and allows the user to enter an email address. However, unless the email is sent or closed without sending, Excel won't let the user do anything else in Outlook, even open the attachment to check it. It doesn't close the file either until the email has been dealt with, so it is stuck in this loop. How can I get around this?

TempFilePath = Environ$("temp") & "\"
TempFileName = "The File Name"
FileExtStr = ".xlsx"

With TheWorkbook
.SaveAs TempFilePath & TempFileName & FileExtStr, FileFormat:=FileFormatNum
On Error Resume Next
For I = 1 To 3
 .SendMail "", _
    "This is the Subject line"
    If Err.Number = 0 Then Exit For
Next I
On Error GoTo 0
.Close SaveChanges:=False
End With

Upvotes: 1

Views: 818

Answers (1)

Siddharth Rout
Siddharth Rout

Reputation: 149305

Instead of .SendMail Why not latebind with Outlook? This way, Excel will not have to wait for the action to be completed?

See this example

Option Explicit

Sub Sample()
    Dim OutApp As Object
    Dim OutMail As Object
    Dim i As Long

    Set OutApp = CreateObject("Outlook.Application")
    Set OutMail = OutApp.CreateItem(0)

    With ThisWorkbook
        '
        '~~> Do Something
        '

        For i = 1 To 3
            Set OutMail = OutApp.CreateItem(0)

            With OutMail
                .Subject = "This is the Subject line"
                .Body = "Hello World"
                .Attachments.Add TempFilePath & TempFileName & FileExtStr

                '~~> Show the email
                .Display
            End With
        Next i
    End With
End Sub

Upvotes: 4

Related Questions