Excel VBA Email Attachment Using OFT Template Stuck in Outbox

npvrader

Board Regular
Joined
Feb 13, 2014
Messages
169
I run this macro from a file that I have stored in dropbox. It creates 40+ emails and works flawlessly at my office, but each time I have run it from my laptop it creates the emails, but the emails are stuck in my outbox until I open them up, individually, and click on the send button. Both computers are using Win7, both are using Office 2010 and both computers have up to date security software. My office desktop is using Microsoft Security Essentials and my laptop is using Norton Live 360. I have noticed that the copy of the emails in my sent files show the email addressee's differently: emails sent from the office show the addressee as
xxx@mailsomewhere.com whereas the emails sent from my laptop show he addressees as 'xxx@mailsomewhere.com' (the single quotes). Any ideas?

By the way, this macro reads from a worksheet that lists the email addressees, the report the addressee is to receive, the outlook template to use and determine if they have unsubscribed.



PHP:
Sub MailReports()
Dim emailRept As String, emailItem As String, addReport As String, emailAddr As String, unSub As String
Dim OutApp As Object, OutMail As Object


' ScreenUpdating = False
Set cSheet = Sheets("PrintAndMail")
Set OutApp = CreateObject("Outlook.Application")

pathToReports = Application.ActiveWorkbook.Path & "\@DPC_Daily_Reports\"
fDate = WorksheetFunction.Text(Range("B3").Value, "MMDDYY")
subjDate = WorksheetFunction.Text(Range("B3").Value, "MM/DD/YY")
lstEmailRow = Range("I2").End(xlDown).Row - 1

' read worksheet with email addresses, the title of the report to attach, the email template to use.....

For i = 1 To lstEmailRow
    emailAddr = Range("I1").Offset(i, 0).Value
    emailRept = Range("I1").Offset(i, 1).Value & ".pdf"
    emailItem = Range("I1").Offset(i, 2).Value
    unSub = Range("I1").Offset(i, -3).Value
    
    If unSub = "C" Then GoTo skipMail
    
    fName = fDate & "_" & emailRept
    addReport = pathToReports & fName
    useOFT = pathToReports & emailItem
    
    Set OutMail = OutApp.CreateItemFromTemplate(useOFT)
        On Error Resume Next
            With OutMail
                .To = emailAddr
                .Subject = "DPC Report for " & subjDate
                .Attachments.Add (addReport)
                .Send
            End With
        On Error GoTo 0
        Set OutMail = Nothing
        
skipMail:
Next i

cleanup:
    Set OutApp = Nothing
    Application.ScreenUpdating = True

End Sub
 

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.
Without any changes made to the above code, it has stopped hanging. Therefore, I suspect there is some environmental issue that was causing my problem. My IMAP is somewhat slow to update folders and my tendency was to open and activate outlook just moments before running the macro - so I have been opening it up and letting the folders update and all seems to be working well now.
 
Upvote 0

Forum statistics

Threads
1,223,276
Messages
6,171,138
Members
452,381
Latest member
Nova88

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top