Command Button to send SS as E-Mail

[brennaboy]

Hi,

I just wondered if it is possible to add a command button to a spreadsheet so that when you click on it, it saves the SS and then attaches it to an E-Mail where you can have the recipients and subject auto populated?

Can anyone offer up any help or guidance?

Many thanks,

Bren.

[Bob Phillips]

Untested


Dim mpOutlook As Object
Dim mpMailItem As Object
Dim mpRecipient As Object
Dim mpNameSpace As Object
ActiveWorkbook.SaveAs "C:\MyDir\MyFile.xls"
Set mpOutlook = CreateObject("Outlook.Application")
Set mpNameSpace = mpOutlook.GetNameSpace("MAPI")
mpNameSpace.Logon , , True
Set mpMailItem = mpOutlook.CreateItem(0) 'olMailItem
Set mpRecipient = mpMailItem.Recipients.Add("someone@somwehere.com")
mpRecipient.Type = 1 'olTo - cc is 2
With mpMailItem
.Subject = "This is my Subject"
.Body = "Please find attached"
.Attachments.Add "C:\MyDir\MyFile.xls"
.Display '<<< this will not send, change to .Send if required
End With
Set mpRecipient = Nothing
Set mpMailItem = Nothing
Set mpNameSpace = Nothing
Set mpOutlook = Nothing

[brennaboy]

That is great!

Do you know if this would work in Word, e.g. if I created a comman button on a userform that would mail the document to a recipient?

Cheers,

B.

[Bob Phillips]

You would have to save it as a word doc, but the code should carry across without problems.

[dilipramadas]

i'm using excel 2007, how do i use the same code for lotus notes?

[Bob Phillips]

No idea, I got involved with Lotus Notes in 1997, it was rubbish, I wouldn't touch it with a barge pole now.

[dilipramadas]

Well... I work with it, so can't complain... and is there wysiwyg for creating these codes? (i'm asking coz i don't VB)

[Bob Phillips]

Kenneth Hobs posted some code for using Lotus Notes earlier this month. Do a search on it.