![]() |
|
|||||||
|
|
|
Thread Tools | Display Modes |
|
#1
|
|||
|
|||
|
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 |