Excel VBA - Attach (Invoice) PDF to Email and Send to Email Address (Based on Cell Value)

Did anyone have any look getting this to work on mac? Love the video but really struggling to get this to work on mac, specifically the error with Set EApp = CreateObject("Outlook.Application"). Thanks

Hi I have a weird problem my .Attachments.Add (path & fname & ".pdf") always comes back with an error

If someone uses this in 2024, use this:
Sub EmailAsPDF()
Dim OutApp As Outlook.Application
Dim OutMail As Outlook.MailItem
' ... other variable declarations

' ... code to assign values to variables

' Export to PDF
' ... code to export to PDF

' ... code to add record to Sheet3

Set OutApp = New Outlook.Application
Set OutMail = OutApp.CreateItem(olMailItem)

With OutMail
.To = Range("B16")
.Subject = "Invoice no: " & InvNo
.Body = "Please find invoice attached."
.Attachments.Add (path & fname & ".pdf")
.Send ' Send the email
End With
End Sub

How do I download the spread sheet

Many thanks on your very detailed explanation and steps

Hi Chester, could explain how you would export invoice(s) or a formatted form(loan draw cover sheets, conditional liens, unconditional liens) that kind of works in a reverse order to the data entry to printed area and flows through the log with the data starting in a table/log and runs through row by row where the formulas populate the print area (like your invoice template) and goes through to the next row down and repeats the process until the last line in your log. So new 6 lines of row entries in your log create 6 invoice PDFs? This would really help me in my line of work where I either enter data into a table or log or manipulate data from somewhere else and paste into a log or table and it creates forms in PDFs with a formulaic naming convention.

You've saved me a lot of hours.

How can we use WhatsApp to send these invoices? Any guide please! Thank You.

Dear Mr. Tugwell, I've followed every step of your video and I've check my scrip is 100% same as yours. Anyway, it cannot send the PDF file via email when I run the marco. It displays only desipte I have amended "Display" to "Send". Please kindly let me know what did I missed or made mistake ? Thank you very much.

Hello Chester,

Your video series is amazing. The only problem came with the email portion.
When I try to email the pdf I get a run time error 429 - ActiveX component can't create object at
Set EApp = CreateObject("Outlook.Application")

can you please help

Hi everyone! I've just spent hours and hours trying to figure out why the VBA .Display function for Microsoft Outlook was not working. This has nothing to do with Mr. Tugwell's excellent coding. It turns out that if you are using Microsoft's new Outlook(New) version of Outlook, this VBA function will not work. It's because Outlook(New) is web based (or something like that, I'm not an expert). I just purchased Office Home & Student 2021 (one-time purchase) for my nephew for $149 to load this program onto. I did not realize that this package did not include Outlook, but you can use the free Outlook(New) with it. If you are purchasing Microsoft Office and want VBA functions to work properly with Outlook, you will need to buy either the Microsoft 365 annual subscription ($69/year), or the Office Home and Business for $249. Please note that everything else in Mr. Tugwell's invoicing program seems to work just fine with Office Home and Student. I'm not hearing good things about Outlook(New). So if you are pulling your hair out too over this, do not buy Office Home and Student. I did contact Microsoft and they told me that since it had been less than 30 days since the purchase, I could get a refund and upgrade to Office Home and Business. So, if the email button is not worth an extra $100 to you, then just leave that button off and move on. You can still create the PDF using the button and email it yourself. Mr. Turgwell, thank you a million times over for this awesome program that I was able to build for my young nephew starting his own business!

Thanks for your time. That was very insightful.

Watching the video, I still find it difficult to refer to the emPL software that sends receipts and salary slips automatically via email or Zalo.

I seem to encounter a problem with ( .To). So on a single cell the Range is ("C11") or on a merged cell is ("C11:H11"). In both cases, when hovering over the highlighted area, it shows the email address. I am confused...

Hey I tried as you have said here but I am getting debug in Set EApp = CreateObject(“Outlook.Application”) - what should I do here? Pls help

cheers for your help , very good. the only problem i cant get around is the following: i get a debug error saying runtime error "287" application - defined or object defined error?? any ways to fix this?

Greetings! Let me start by saying thank you for the amazing series. Great explanation and easy to follow along. I got a question. If I wanted to personalized the email body with a greeting to the client and display the client's name with the variable set for it. How would one go about that if possible. Thanks anticipated for your time.

I did this and the email is not going through... i send it and i never recieved it... is it because it is an html?

These videos are excellent. Was wondering, once the pdf is created and it is posted to the record of invoices, is there a way to regenerate the invoice to mark it as paid and send the receipt by email?

Mr. Chester,
My name is Rosie. I have a problem with this macro. When I tried running it, my malwarebytes blocked it. It detected an exploit and I couldnt run the macro

When mine makes the pdf, it is a mess of letters, you can't even read it. Can you advise me?

Hello, thanks for this brilliant setup! I just wished these macros worked in Google Sheets. Has anyone here tried to replicate the macro of this template in Google Sheets? This would semplify ten-fold my workflow. Thank you in advance.

i have this debug with this code:
Set EItem = Eapp.CreateItem(0)

Thank you so much for this useful info

Thank you for this series, you have truly helped me develop an invoice system. I have added features, like to not recording duplicates and ensuring that all important fields are entered before recording or saving the invoice. Something were a bit tough, especially as a mac use, but I managed to make it work accordingly.

Can I use gmail instead of outlook, sir?

What a brilliant series. Really well worked out and narrated. Thank you.

What about the duplicate records in the list since if you send to both pdf and save as xlsx file, it creates two rows?

Easy, short and straight to the point

Excellent series, thank you so much!!

I tried this, but i keep getting an error with the "invno = Range("#"). Also, can I use this with AT&T Yahoo.Mail?

Mr. Chester, when I push the macro buttons to record in my record of invoice sheet. It records the invoice, but it creates a new line for each macro I push. It doesn't put all the information for each macro on one line. Can you please help?

With sending via email, how do I code it for gmail and not outlook?

I've learned a lot by watching this series of VBA videos, thank you. Like a lot of others, I do not use outlook. I use Mozilla Thunderbird as my email client. Could you tell me what I need to change to send the invoice from Thunderbird?

Thanks so much! Very easy to follow along

A thousand thanks for providing us with this gem of a series. i will also add my small contribution by sharing what i found. For anybody who like me was looking for a way to add a line break to the email body, you can use the special character sequence 'vbCrLf'.

You can also double it or more to increase the space between the lines.

For example :

.Body = "Hello," & vbCrLf & "Please find attached the document mentioned above." & vbCrLf & vbCrLf & "Best regards."

Hope it works for everybody

super

Can anybody tell me why this comes up with an error saying it cannot find the file?

It does not work on a MAC machine. Microsoft note "Outlook for Mac and OneNote for Mac don't support VBA." Is there a way to overcome this. Thanks

I really like this idea and the functionality behind it. However, I am having issues getting the macros to work. (I am positive it is my own lack of knowledge). I can't seem to get the "Save as .xlsx", "Save as pdf", or "Email as pdf" buttons to work. If anyone is willing to help me get this set up, I would be extremely grateful.

I love this videos but I am in dire need of help pleaseee! I am using a Mac book so I decided to use the mail application instead of outlook. I am using the code below but the pdf is not attaching to the email. I am not sure what I am doing wrong Sub SaveAsPdfAndSendEmail()  

Sub SaveAsPdfAndSendEmail()  
Dim path As String  
Dim invno As Long  
Dim custname As String
Dim fname As String  
Dim amt As Currency  
Dim dt_issue As Date  
Dim term As Variant  
' Set the file path and other variables  
path = "/users/keenyabrowne/desktop/steven file/"  
invno = Range("C3").Value  
custname = Range("B8").Value  
fname = invno & " - " & custname  
' Construct the AppleScript code  
Dim applescriptCode As String  
applescriptCode = "tell application ""Mail""" & vbNewLine  
applescriptCode = applescriptCode & "set Eitem to make new outgoing message with properties {subject:""Invoice for: " & custname & """, content:""Please see attached From, SM.""}" & vbNewLine  
applescriptCode = applescriptCode & "tell Eitem" & vbNewLine  
applescriptCode = applescriptCode & "make new to recipient at end of to recipients with properties {address:""" & Sheets("Invoice for Printing").Range("B12").Value & """}" & vbNewLine  
applescriptCode = applescriptCode & "tell content" & vbNewLine  
applescriptCode = applescriptCode & "make new attachment at after last paragraph with properties {file name:""" & path & fname & ".pdf""}" & vbNewLine  
applescriptCode = applescriptCode & "end tell" & vbNewLine  
applescriptCode = applescriptCode & "end tell" & vbNewLine  
applescriptCode = applescriptCode & "activate" & vbNewLine  
applescriptCode = applescriptCode & "end tell"  
' Execute the AppleScript code MacScript (applescriptCode)
End Sub

Hi!. I am getting an error in the vba statement Set EItem = EApp.CreateItem(0). could you please clarify

Thank you for the great videos, this worked perfectly for my invoicing.

I truly appreciate your teachings and your tools. This tool is perfect for my small business. I was watching you vidoes and planning to create one from scratch, but then I was wondering if you would be willing to sell a copy of the workbook? I would have to do some customizing but it would save me a lot of time. If you would be interested in selling me a copy please let me know how much.

On running the macro I get Run-time error ‘429’:
ActiveX component can’t create object

Amazing instructions from video 1 to 6! Thank you for your help 😊

I’ve found that if the customer table doesn’t have all sections filled in about a customer they show up on the invoice as zeroes instead as blank like it does when no customer is selected and your method to remove the N/A. How do I resolve this issue ?
Thanks

I have been looking for ages for a way to attach e mail to invoices, this is really helpful and it works!!! Thank you for your Professional contribution.

Thanks, excellent tutorials so handy and well created.

A similar sort for cash books would be fantastic as well even better if invoices and spending all linked

Hi Chester, these videos are amazing and I have so enjoyed creating my invoices along with the videos, thank you so much for sharing. I was just wondering if there is a way that all the Record of Invoices boxes can be filled in with one button, so that all the invoice info, Invoice saved as PDF, Invoice saved as excel file and invoice emailed date, are done in one go, Thank you again for the information and videos