|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Hyperlink in excel opens email...can a macro fill in and insert attachments?
I'm issuing client billing via email. Depending on their preference, they receive attached bills via email (Outlook 2010) in PDF or Excel 2007 format. I just began using the quick insert button to fill in the email body, since every month, it's the same. This will save tons of time.
I use and write macros in excel, but haven't tried to link it all together. Now that the idea is there, can someone help me figure out if it's possible? I know I can run a macro from Outlook, but am not sure if that's how it's go about it, or is there a way for the excel hyperlink to open the email, select the designated quick text, insert the attachment then stop to be reviewed before being sent? |
#2
|
||||
|
||||
The short answer is no, but you can do it using an Excel macro to create the message and fill in whatever you require.
If the message is the same each time, then you could create an Outlook template with the body text and the subject already in place, or you could use the macro to create the message from scratch. It just means the code is a bit longer. The following users the longer version. You can probably get the recipient, the attachment and the subject - even personalise the message body, using data from your worksheet. Code:
Sub SendInvoice() Dim oOutlookApp As Object Dim oItem As Object Dim olInsp As Object Dim wdDoc As Object Dim oRng As Object On Error Resume Next 'Get Outlook if it's running Set oOutlookApp = GetObject(, "Outlook.Application") 'Outlook wasn't running, start it from code If Err <> 0 Then Set oOutlookApp = CreateObject("Outlook.Application") End If 'Create a new mailitem Set oItem = oOutlookApp.CreateItem(0) With oItem .BodyFormat = 2 Set olInsp = .GetInspector Set wdDoc = olInsp.WordEditor Set oRng = wdDoc.Range oRng.collapse 1 oRng.Text = "This is the message body" .To = "someone@somewhere.com" .Subject = "This is the subject" .Attachments.Add "C:\Path\Invoice No.001.pdf" .Display End With 'Clean up Set oItem = Nothing Set oOutlookApp = Nothing Set olInsp = Nothing Set wdDoc = Nothing lbl_Exit: Exit Sub End Sub
__________________
Graham Mayor - MS MVP (Word) (2002-2019) Visit my web site for more programming tips and ready made processes www.gmayor.com |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Email Hyperlink to excel worksheet | Shaz | Outlook | 2 | 08-03-2014 11:46 PM |
Cant unformat email address hyperlink in Excel the way HELP says I can | tomseeley | Excel | 11 | 01-24-2014 05:50 PM |
Insert a hyperlink in body of email | ChuckDrago | Outlook | 0 | 06-28-2013 06:51 AM |
Word macro to email hyperlink | pooley343 | Word VBA | 0 | 07-20-2011 01:48 AM |
Trying to insert hyperlink to excel file into Outlook | KarenE | Outlook | 0 | 05-08-2006 12:56 PM |