Excel Macro send email

Question

Hello there, i have a report that i would like to send via excel. it will include the recipitents, subject and the information in the body. actually it could copy the cells in question. what i did so far is create a button and assign a macro to it with this code:

Private Sub CommandButton1_Click()
 Application.Dialogs(xlDialogSendMail).Show arg1:=Sheets("Sheet1").Range("E3"), _
                      arg2:=Sheets("Sheet1").Range("E7")

End Sub

the problem is that this command sends the workbook as attachment.

can someone help me with the code that will allow me to do this.

thanks a million!

cheers

Answer 1

Add a reference to the outlook com library; then you can copy/paste values & formatting what you need to a new sheet, then copy that into the outlook mail.

Answer 2

Set a reference to the "Microsoft Outlook xx.x Object Library" and you can use this code as an example of what to do to build or send an email:

As it is it will just display the email without sending. You can comment out the .display line and uncomment the .send to just send it.

Sub EmailFromExcel()
    On Error GoTo PROC_EXIT
    Dim OL As New Outlook.Application

    Dim olMail As Outlook.MailItem
    Set olMail = OL.CreateItem(olMailItem)

    Dim SrcSheet As Excel.Worksheet
    Set SrcSheet = Sheets("Sheet1")

    With olMail
        .To = SrcSheet.Range("E3").Text
        .Subject = SrcSheet.Range("E7").Text
        .Body = SrcSheet.Range("E12").Text
        .Display vbModal
        '.Send
    End With

 PROC_EXIT:
    On Error GoTo 0
    OL.Quit
    Set OL = Nothing
End Sub

Answer 3

hi there, ive added the outlook object library 11 in the vba. Also i was getting some errors with the code Jon posted. i believe there was missing a end sub which is added and i removed: On Error GoTo PROC_EXIT because i was getting an error with both of this correction, i finally got the email pop up with the information needed to send.

so unless i did somthing wrong by removing proc_exit it seems to work now.

however if i need to add to the outlook body informtation that is found in excel in this interval C6:K41.( 9 X 35 ) is it possible to just copy the information as is or link it to the body?

thanks