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

Hello Elise,

It didnt work and both if I use my own code or your code it gives me the following error:

Any idea what I can do? Am I writing the code from the link: https://wellsr.com/vba/2018/excel/excel-vba-send-email-with-attachment/ incorrectly perhaps?

I am not an expert on this...truly sorry, but I really appreciate your assistance!!

Answer 2

It looks like the line that is now causing an issue is:

source_file = "C:\Work Files\" & Cells(j, 3)

Can you check this actually is a valid file path it is looking up here, so all your files should be in C:\Work Files and your filenames should be in column C

Kind Regards,

Elise

Answer 3

Hello Elise,

I put in my source file. However, now I am getting the same error again:

I have checked microsoft outlook 16.0 in my references

This is so incredibly confusing... I dont get what I am doing wrong? I copy pasted the entire code from the website and inserted my own source file, surely it should work then??

Answer 4

It's spelled INTEGER, not INTERGER and you need to declare both types or the i ends up being a variant.

Dim i as integer, j as integer

Better as,

Dim i as long, j as long

If you do it right, the integer becomes Integer.

BTW, what happens when you put ?dir "C:\Work Files" & Cells(j, 3) into the VBA editor's Immediate window (Ctrl+G) and tap Enter?

Answer 5

Hello Jeeped,

Thanks for the assistance!

I got some help from Mrexcel.com and was given this formula:

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 "O" email addresses

    If Sheets("Sheet1").Range("O2").Value <> "" Then

        nList = nList & ";" & Sheets("Sheet1").Range("O" & 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

            .Send 'Send email, change to send to send straight away use

End With

End Sub

This works! However, if you (or anybody else here) knows how to convert this into a Command Button in my Excel sheet1 where I can click on the button in order to execute this order, after which the button will say something like "Order submitted & Email sent. Thank you." that would be absolutely amazing!

Thank you so much for your time :)

Best regards,

David