ExcelBanter - VBA Button to Send Email

ExcelBanter (https://www.excelbanter.com/)

- Excel Programming (https://www.excelbanter.com/excel-programming/)

- - VBA Button to Send Email (https://www.excelbanter.com/excel-programming/297786-vba-button-send-email.html)

VBA Button to Send Email

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 =
")
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

VBA Button to Send Email

VBA Button to Send Email

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
"Dave D." wrote in message
. com...
|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 =
| ")
| 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

.

VBA Button to Send Email

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
"Dave D." wrote in message
...
| 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
| "Dave D." wrote in message
| . com...
| |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 =
| | ")
| | 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
|
|
| .
|

VBA Button to Send Email

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

--
Regards Ron de Bruin
http://www.rondebruin.nl

"Dave D." wrote in message ...
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
"Dave D." wrote in message
. com...
|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 =
| ")
| 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

.