Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Prompt the start of an email...user fills in email address
There is a lot of great information for sending emails...but all of
them seem to do one of two things...or both. Either they automatically send the email (no prompt) and/or it's to attach the workbook they are in. What I want to do... My workbook has the option for my users to send a permit application package. I want the user to click a macro assigned button that will start an email (Outlook) with just a subject (New Permit Application Package) and body (Attached is the permit application package you will need to submit for a new permit.) and then attach two files (a pdf, and a word document). The macro would then unload and the user is left to fill in the To line. Then when the user is ready, they themselves click the Send button. I know this should be relatively easy...just can't seem to figure it out. Ron has a lot of good information but either I can't piece it together to make it work or I have a lot of errors in my own tweaking. Any help would be greatly appreciated. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Prompt the start of an email...user fills in email address
On Jul 16, 7:57*am, gab1972 wrote:
There is a lot of great information for sending emails...but all of them seem to do one of two things...or both. *Either they automatically send the email (no prompt) and/or it's to attach the workbook they are in. What I want to do... My workbook has the option for my users to send a permit application package. *I want the user to click a macro assigned button that will start an email (Outlook) with just a subject (New Permit Application Package) and body (Attached is the permit application package you will need to submit for a new permit.) and then attach two files (a pdf, and a word document). *The macro would then unload and the user is left to fill in the To line. *Then when the user is ready, they themselves click the Send button. *I know this should be relatively easy...just can't seem to figure it out. *Ron has a lot of good information but either I can't piece it together to make it work or I have a lot of errors in my own tweaking. *Any help would be greatly appreciated. Okay...so I attempted to tackle this on my own (with some reference help...thanks Ron, Oz, and D Peterson). Here is what I got to work in case anyone is interested. It allows me to not have to store email addresses and file names in cells because email addresses could be different and my attachments are canned. This also allows the user to view the email before it is sent to maybe add something extra in the body. This was just a way to get the process started so the user wouldn't have to go searching for the files to be added. Option Explicit Sub SendEmail() Dim wkb As Workbook Dim wks As Worksheet Dim rng As Range Dim olMyApp As Outlook.Application Dim olMyEmail As Outlook.MailItem Set wkb = ThisWorkbook Set wks = wkb.Worksheets("Sheet3") Set rng = wks.Range("A2") Set olMyApp = New Outlook.Application Set olMyEmail = olMyApp.CreateItem(olMailItem) olMyEmail.Subject = "Permit Application Package" olMyEmail.Body = "Attached is a permit application package" olMyEmail.Attachments.Add ("C:\Temp\testpic.png") olMyEmail.Attachments.Add ("C:\My Documents\testlog.log") olMyEmail.Display Set olMyApp = Nothing Set olMyEmail = Nothing End Sub The only thing I don't know yet is if everyone needs to have the Microsoft Outlook reference added in VBE. Anyone care to elaborate? Maybe have a simpler solution? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Prompt the start of an email...user fills in email address
With Outlook object model code you can use .Display instead of .Send
-- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "gab1972" wrote in message ... There is a lot of great information for sending emails...but all of them seem to do one of two things...or both. Either they automatically send the email (no prompt) and/or it's to attach the workbook they are in. What I want to do... My workbook has the option for my users to send a permit application package. I want the user to click a macro assigned button that will start an email (Outlook) with just a subject (New Permit Application Package) and body (Attached is the permit application package you will need to submit for a new permit.) and then attach two files (a pdf, and a word document). The macro would then unload and the user is left to fill in the To line. Then when the user is ready, they themselves click the Send button. I know this should be relatively easy...just can't seem to figure it out. Ron has a lot of good information but either I can't piece it together to make it work or I have a lot of errors in my own tweaking. Any help would be greatly appreciated. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Send data from Excel in email from specific email address | Excel Programming | |||
send wkbk as an email attachment with an email address copied from | Excel Discussion (Misc queries) | |||
can I copy a column of email addresses, paste into email address? | New Users to Excel | |||
Transfer Email addresses from spreadsheet to email address book | Excel Discussion (Misc queries) | |||
Find User Default Email Address in Outlook | Excel Programming |