1

I'm really having trouble finding any answers for this problem. I have an Excel macro that filters a sheet (it's a basic order form), copies and emails a range using an Outlook object. The file worked for several weeks and ran quickly.

Now all of the sudden whenever the macro is run the Excel portion of filtering and copying works fine but when it gets to the email code Outlook locks up, and I get a popup from Excel saying it's waiting for Outlook to complete an OLE action. I end up having to kill the Outlook process. I've tried early and late bindings.

Sub EmailOrder()

Dim answer As Integer
Dim lastRow As String
Dim filteredRow
Dim emailApp As Outlook.Application
Dim emailItem As Outlook.MailItem
Dim exportRange As Range
Dim currentTime As String
Dim currentUserEmailAddress As String

answer = MsgBox("Click OK to send your order to the supply team", vbOKCancel)

    If answer = vbOK Then
        Worksheets("Sheet1").Activate
        
            lastRow = Cells(Rows.Count, 1).End(xlUp).Row
            currentTable = "$A$1:$E$" & lastRow
            'Filter out blanks
            Range(currentTable).AutoFilter Field:=5, Criteria1:="<>"
            
            Set exportRange = Nothing
            On Error Resume Next
            'Only the visible cells in the selection
            Set exportRange = Selection.SpecialCells(xlCellTypeVisible)
            
        'Setup outlook objects and mail
        Set emailApp = New Outlook.Application
        Set emailItem = emailApp.CreateItem(olMailItem)
        Set outSession = emailItem.Session.CurrentUser
        currentUserEmailAddress = outSession.AddressEntry.GetExchangeUser().PrimarySmtpAddress
        currentTime = Now
        
        'Write email
        With emailItem
        
        .To = "redacted@gmail.com"
        .CC = currentUserEmailAddress
        .Subject = "Local Inventory Order " & currentTime
        .HTMLBody = RangetoHTML(exportRange)
        .Send
        End With
            
        'Close objects
        Set emailApp = Nothing
        Set emailItem = Nothing
        
        MsgBox ("The order has been emailed to the supply team.")
    End If
End Sub

The RangetoHTML function is from Ron de Bruin's website. Any help is appreciated.

EDIT: failed to mention that there have been other users of the sheet who reported it working for several weeks then stopping.

therocketman93
  • 11
  • 2
  • Has any security pop up appeared in outlook advising of programatic access? Try `emailApp.visible=true` to show the outlook – Nathan_Sav Apr 21 '22 at 13:14
  • @Nathan_Sav I've seen the popup before but it could have been on client's computers. Outlook trust center setting is to notification for all macros. I tried `emailApp.visible=true` but there was no difference. – therocketman93 Apr 21 '22 at 14:06
  • There won't be a difference, just you will see outlook so switch to outlook see if there is any message there. Try just displaying the message rather than sending it too, so if that helps – Nathan_Sav Apr 21 '22 at 14:09
  • 1
    @Nathan_Sav - it is a good idea to use `Display` instead of `Send` to avoid the security prompt, but the script above will trigger the prompt even before that as it accesses `emailItem.Session.CurrentUser` – Dmitry Streblechenko Apr 21 '22 at 15:28
  • `On Error Resume Next` is misused about 99.999999% of the time https://stackoverflow.com/a/31753321/1571407. Remove it to see if you get an error message prior to Outlook freezing. – niton Apr 22 '22 at 03:51

0 Answers0