Send out email through command button and macros

[David77]

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

 

[decadence]

You need "Microsoft Outlook 16.0 Object Library" in your references,

However you may need to browse for it when adding references, so depending on where Microsoft office is installed it will either be in C:\Program Files or C:\Program Files (x86)

"C:\Program Files (x86)\Microsoft Office\Root\VFS\ProgramFilesCommonX86\Microsoft Shared\OFFICE16\MSO.dll"

"C:\Program Files\Microsoft Office\Root\VFS\ProgramFilesCommonX86\Microsoft Shared\OFFICE16\MSO.dll"

 

[David77]

You need "Microsoft Outlook 16.0 Object Library" in your references,

However you may need to browse for it when adding references, so depending on where Microsoft office is installed it will either be in C:\Program Files or C:\Program Files (x86)

"C:\Program Files (x86)\Microsoft Office\Root\VFS\ProgramFilesCommonX86\Microsoft Shared\OFFICE16\MSO.dll"

"C:\Program Files\Microsoft Office\Root\VFS\ProgramFilesCommonX86\Microsoft Shared\OFFICE16\MSO.dll"

Hello Decadence,

Thank you so much for your quick answer!

Unfortunately, my references are greyed out for some reason. Any solution to this issue?

Best regards,
David

 

[David77]

You need "Microsoft Outlook 16.0 Object Library" in your references,

However you may need to browse for it when adding references, so depending on where Microsoft office is installed it will either be in C:\Program Files or C:\Program Files (x86)

"C:\Program Files (x86)\Microsoft Office\Root\VFS\ProgramFilesCommonX86\Microsoft Shared\OFFICE16\MSO.dll"

"C:\Program Files\Microsoft Office\Root\VFS\ProgramFilesCommonX86\Microsoft Shared\OFFICE16\MSO.dll"

Update!

I fixed the references issue, but I can see that I already have Microsoft Office 16.0 Object Library checked out:

 

[Trevor G]

Your code is in Break mode that's why you can't look for the Outlook Reference. You need to reset the code (blue square button)

 

[decadence]

You need Microsoft "Outlook" Library, your reference shows Microsoft "Office" Library

 

[David77]

Your code is in Break mode that's why you can't look for the Outlook Reference. You need to reset the code (blue square button)

You need Microsoft "Outlook" Library, your reference shows Microsoft "Office" Library

Hello Trevor & Decadence,

Thank you so much for your time. It is greatly appreciated!

I seem to have fixed the old error by adding Microsoft Outlook 2016 to references.

However, when I run it now it does nothing and when I try again it produces the following error:

Can you please verify whether I inserted my email (written as: Test@mail.com for now) correctly? It did not send me an email when I inserted my own email there before.

And how do I fix this error?

Thank you!

Best regards,
David

 

[Trevor G]

Your email address need to have a semicolon between each address.

In your source_file you have it written to look in a cell as well as using thisworkbook.fullname. I have checked that the following code will display an email to a list of people and also attached the workbook it is written in.

Sub Send_Emails_To_and_CC()
Dim ESubject, EBody As String, i As Integer
Dim MObject, nList As String, n2 As String, o As Variant
        
         For i = 2 To 4 'use cells 2 to 4 in column "A" email addresses
    If Sheets("Sheet1").Range("A2").Value <> "" Then
        nList = nList & ";" & Sheets("Sheet1").Range("A" & i).Value
    End If
     If Sheets("Sheet1").Range("B2").Value <> "" Then
        nList2 = nList2 & ";" & Sheets("Sheet1").Range("B" & i).Value
    End If
Next
ActiveWorkbook.Save
       Set Mail_Object = CreateObject("Outlook.Application")
        With Mail_Object.CreateItem(o)
            .Subject = "Files"
            .To = nList
            .cc = nList2
            .Body = "Something goes in here"
            .Attachments.Add ActiveWorkbook.FullName 'ActiveWorkbook.FullName
            .display 'display email, change to send to send straight away use
End With
End Sub

 

[David77]

He

Your email address need to have a semicolon between each address.

In your source_file you have it written to look in a cell as well as using thisworkbook.fullname. I have checked that the following code will display an email to a list of people and also attached the workbook it is written in.

Sub Send_Emails_To_and_CC()
Dim ESubject, EBody As String, i As Integer
Dim MObject, nList As String, n2 As String, o As Variant
       
         For i = 2 To 4 'use cells 2 to 4 in column "A" email addresses
    If Sheets("Sheet1").Range("A2").Value <> "" Then
        nList = nList & ";" & Sheets("Sheet1").Range("A" & i).Value
    End If
     If Sheets("Sheet1").Range("B2").Value <> "" Then
        nList2 = nList2 & ";" & Sheets("Sheet1").Range("B" & i).Value
    End If
Next
ActiveWorkbook.Save
       Set Mail_Object = CreateObject("Outlook.Application")
        With Mail_Object.CreateItem(o)
            .Subject = "Files"
            .To = nList
            .cc = nList2
            .Body = "Something goes in here"
            .Attachments.Add ActiveWorkbook.FullName 'ActiveWorkbook.FullName
            .display 'display email, change to send to send straight away use
End With
End Sub

Hello Trevor,

I copied your code and this is ABSOLUTELY amazing! Thank you so so much!!!

I am now getting this:

Is there any way that I can change it so it sends out the email automatically when I click on a command button in the Excel sheet that I name "Submit" or "Submit Order" for example?

Thank you so much once again!

Best regards,
David

 

[Trevor G]

In the code David change the word

.Display

to

.Send