Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Won't send email unless Outlook is already open


I downloaded your "Auto-Invoice email with button click" from your site.  I massaged it to conform to my appliocation.

The problem is that it hanges up on "..Attachment.Add newFilename", if Outlook is not already open and has oppened at least one existing email.  (Strange).  If I go ahead and open Outlook, open and existing email, it runs fine.  What the heck is going on?  If I go to debug and put the cursor on the highlighted error, it will show the complete file name.  {newFilename = "2023-08-10 - IBEW Retired Members Club 479 Tally Sheet.PDF"}  Like I said, it runs great if Outlook is already open.  If Outlook is not open, I get this message when i run the macro.

Run-time error '-2147024894 (800700002)'  Cannot find this file.  Verify the path and the file name are correct.

Option Explicit
Sub Email_Tally()
' https://www.teachexcel.com/
'' Full VBA/Macro Course: https://www.teachexcel.com/vba-course-update.php?src=tut_file
' Email PDF
    ' Variables
    Dim outlookApp As Object
    Dim emailItem As Object
    Dim emailSubject As String
    Dim newFilename As String
    Dim emailBody As String
    Dim MyPath As String
     ' Get a reference to the Outlook Application Object.
    Set outlookApp = CreateObject("Outlook.Application")
    Set emailItem = outlookApp.CreateItem(0)
    ' Make a filename.
    newFilename = Cells(15, "F").Text & " - " & Cells(16, "D").Value & ".PDF" 
    ' Make the PDF.
        ActiveSheet.ExportAsFixedFormat _
        Type:=xlTypePDF, _
        Filename:=newFilename, _
        Quality:=xlQualityStandard, _
        IgnorePrintAreas:=False, _
    ' Email Section
    ' Setup values for the email.
    emailSubject = "IBEW Retired Members Club #479 Tally Sheet"
    emailBody = "Attached is the IBEW Retired Members Club #479 Tally Sheet" & vbNewLine
    ' Build the Email
    With emailItem
        .To = Worksheets("Tally Sheet").Range("Tally_Email").Value
        .Bcc = Worksheets("Tally Sheet").Range("BlindCopyEmail").Value
        .Subject = emailSubject
        .Body = emailBody
        .Attachments.Add newFilename
        .Display ' or .Send
    End With
        MsgBox "OK, the Tally  Sheet email is sent! "
End Sub
Post Edited
CODE Tags: You must add [CODE][/CODE] tags around your code! (click the CODE button to do this when creating a post)
Tags: Tags were updated to reflect the topic of the question.


Selected Answer

Hello orkrj and welcome to the forum,

From what I can see in your code is that the line "newFileman = " is missing the file path component.

 "C:\Test\Export\Invoice - " 

I suspect this happened when you modified it to suit your situation. That line of code, as shown in the tutorial, should have the following structure:

 ' Make a filename.
    newFilename = "C:\Test\Export\Invoice - " & _
        Worksheets("Invoice").Range("InvoiceID").Value & ".pdf"

Here is the link to the tutorial:

Tutorial - send emails from Excel

If this solves your problem please mark the answer as selected.

Cheers   :-)


Answer the Question

You must create an account to use the forum. Create an Account or Login