VBA Button to Send Email

[Dave D.]

I want to have the user click the button and send the current workbook
as an attachement to certian users. I can get to bring up Outlook and
have it populate my certain email addresses by using:

....
Set oMailItem = oOutlook.CreateItem(0)
With oMailItem
Set oRecipient =
..Recipients.Add("(e-mail address removed);[email protected]")
oRecipient.Type = 1
.Subject = "Agent Form Data for " & emailDate
.Body = "This Agent was submitted on: " & emailDate
.Attachments.Add sAttachment
.Display
....

but when using this I've been trying to use
..attachments.add "some local drive file" but can't use activeworkbook
or anything. Any thoughts?

Thanks in Advance

Dave

 

[Jim Rech]

..Attachments.Add ThisWorkbook.FullName

should do it.

--
Jim Rech
Excel MVP
|I want to have the user click the button and send the current workbook
| as an attachement to certian users. I can get to bring up Outlook and
| have it populate my certain email addresses by using:
|
| ...
| Set oMailItem = oOutlook.CreateItem(0)
| With oMailItem
| Set oRecipient =
| .Recipients.Add("(e-mail address removed);[email protected]")
| oRecipient.Type = 1
| .Subject = "Agent Form Data for " & emailDate
| .Body = "This Agent was submitted on: " & emailDate
| .Attachments.Add sAttachment
| .Display
| ...
|
| but when using this I've been trying to use
| .attachments.add "some local drive file" but can't use activeworkbook
| or anything. Any thoughts?
|
| Thanks in Advance
|
| Dave

 

[Dave D.]

Hi Jim -

That code works great, it attaches the file, but the
saved version. I have people filling out information,
then I want them to click the button send the information
they typed in. Basically do the exact same thing as if
they went up to File-->Send-->File as Attachment I have
menu's locked and such as thats why the users can't just
do that instead of clicking on my email button.

Thanks Jim in Advance

Dave D.

 

[Jim Rech]

I guess you're going to have to force a save if needed before you attach the
file:

If Not ThisWorkbook.Saved Then ThisWorkbook.Save

--
Jim Rech
Excel MVP
| Hi Jim -
|
| That code works great, it attaches the file, but the
| saved version. I have people filling out information,
| then I want them to click the button send the information
| they typed in. Basically do the exact same thing as if
| they went up to File-->Send-->File as Attachment I have
| menu's locked and such as thats why the users can't just
| do that instead of clicking on my email button.
|
| Thanks Jim in Advance
|
| Dave D.
| >-----Original Message-----
| >..Attachments.Add ThisWorkbook.FullName
| >
| >should do it.
| >
| >--
| >Jim Rech
| >Excel MVP
| >| >|I want to have the user click the button and send the
| current workbook
| >| as an attachement to certian users. I can get to bring
| up Outlook and
| >| have it populate my certain email addresses by using:
| >|
| >| ...
| >| Set oMailItem = oOutlook.CreateItem(0)
| >| With oMailItem
| >| Set oRecipient =
| >| .Recipients.Add("(e-mail address removed);[email protected]")
| >| oRecipient.Type = 1
| >| .Subject = "Agent Form Data for " &
| emailDate
| >| .Body = "This Agent was submitted on: " &
| emailDate
| >| .Attachments.Add sAttachment
| >| .Display
| >| ...
| >|
| >| but when using this I've been trying to use
| >| .attachments.add "some local drive file" but can't use
| activeworkbook
| >| or anything. Any thoughts?
| >|
| >| Thanks in Advance
| >|
| >| Dave
| >
| >
| >.
| >

 

[Ron de Bruin]

If you use SendMail code it will do what you want
http://www.rondebruin.nl/sendmail.htm#Workbook