Quick question on sending emails via Excel

[lopiteaux]

Hi all,
I've got a little bit of code in place that creates an email from Excel. It's fairly basic, and I'd like to add the following three functions to it:

1) Add the current workbook as an attachment,

or (these do not necessarily need to work on the same message)

2) Add a predefined range as the MESSAGE BODY (i.e. File > Send To > Mail Recipient > Send the current sheet as the message body)

3) Add the signature stored in Outlook

4) Send it off.

This is the code I have sofar - as I said, real basic.

Sub SendEmail()
    Dim OutlookApp As Object, mail As Object
    Dim msgbody As String
    
    Set OutlookApp = CreateObject("Outlook.Application")
    
    msgbody = "Please find attached today's report." & vbCrLf & vbCrLf
    msgbody = msgbody & "Kind regards," & vbCrLf & vbCrLf
    
    Set mail = OutlookApp.CreateItem(0)
    With mail
        .SentOnBehalfOfName = "SENDER"
        .To = "RECIPIENT"
        .CC = "CCRECIPIENT"
        .Subject = "Report for COB " & Format(Date - 1, "dd/mm/yyyy")
        .body = msgbody
        .display
    End With
End Sub

Appreciate any help, thanks!

lopiteaux

 

[moonfish]

I'm not sure if this is useful to you but this is what Excel does if you use its built-in send by email option:

Application.Dialogs(xlDialogSendMail).Show

 

[TiaXL]

sub email()
 
Dim outapp As Object
Dim outmail As Object
dim sBody as string
 
sBody = range("b3").value
 

Set outapp = CreateObject("Outlook.Application")
Set outmail = outapp.createitem(0)
With outmail
        .To = ""
         .CC = ""
        .Subject = sBody        
        .body = ""
        .Attachments.Add ""
        .display
        SendKeys "%{s}", True 
End With
Application.Wait (Now + TimeValue("00:00:05"))
SendKeys "%{s}", True 
Set outapp = Nothing
Set outmail = Nothing

 

[lopiteaux]

Thanks for the quick response TiaXL... I am able to add the attachment when a path is specified (in this case this works perfectly), so thanks a lot. It's pretty obvious, can't believe I didn't spot that one...

As to the signature and no. 2, sending it as the message body in Excel, any thoughts?

@moonfish, I've come across that method a few times but cannot seem to find the same functionality and flexibility that the method below gives me... are you able to add anything except a addressee and subject using your method (it IS much cleaner)?

 

[daverunt]

Attachments

http://www.dicks-clicks.com/excel/olSending.htm#Using_HTML_in_Message_Body

Signature
http://www.rondebruin.nl/mail/folder3/signature.htm

 

[lopiteaux]

Thanks daverunt, that put the last few pieces together!