Send out email through command button and macros

Question

Hello everybody,

First of, please note that I am quite new at VBA coding and thus have extremely limited experience here.

I am currently trying to create an Excel workbook where customers can fill in their orders and "Submit" this to by pressing a button after which the order is sent by email to me.

I was figuring that the best way to do so would be to create a command button linked to a macro. However, this seems to be extremely difficult and I have naturally tried googling this to find solutions. Most of the solutions I found were able to create macros that sent out emails, but not emails that included the excel file as an attachment too.

In order to include the excel file as an attachment, I found the following link: Using Excel VBA to Send Emails with Attachments - wellsr.com which apparently should have solution

However, when I try to run this macro in my own excel file, it comes with the following error:

Does anybody here have any idea how I can fix this or provide a better solution to my problem? It would truly be much appreciated!

Thank you so much for your time, whoever may have read this.

Best regards,

David

Answer 1

This is the code I got working:

Sub send_email_complete()
Dim outlookApp As Object
Dim myMail As Object
Set outlookApp = CreateObject("Outlook.application")
Set myMail = outlookApp.CreateItem(0)
Dim source_file, to_emails, cc_emails As String
Dim i, j As Integer

For i = 2 To 4
    to_emails = to_emails & Cells(i, 1) & ";"
    cc_emails = cc_emails & Cells(i, 2) & ";"
Next i

For j = 2 To 5
    source_file = "C:\Work Files\" & Cells(j, 3)
    myMail.Attachments.Add source_file
Next

ThisWorkbook.Save
source_file = ThisWorkbook.FullName
myMail.Attachments.Add source_file

myMail.CC = cc_emails
myMail.To = to_emails
myMail.Subject = "Files for Everyone"
myMail.Body = "Hi Everyone," & vbNewLine & "Please read these before the meeting." & vbNewLine & "Thanks"

myMail.Display

End Sub

You need to make sure you have the filename in C2:C5 though.

Doesn't need any references either.

Kind Regards,

Elise