Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 09-16-2014, 05:53 PM
Tammypi Tammypi is offline Hyperlink in excel opens email...can a macro fill in and insert attachments? Windows XP Hyperlink in excel opens email...can a macro fill in and insert attachments? Office 2010 32bit
Novice
Hyperlink in excel opens email...can a macro fill in and insert attachments?
 
Join Date: Sep 2014
Posts: 1
Tammypi is on a distinguished road
Default 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?
Reply With Quote
  #2  
Old 09-16-2014, 10:36 PM
gmayor's Avatar
gmayor gmayor is offline Hyperlink in excel opens email...can a macro fill in and insert attachments? Windows 7 64bit Hyperlink in excel opens email...can a macro fill in and insert attachments? Office 2010 32bit
Expert
 
Join Date: Aug 2014
Posts: 4,101
gmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud of
Default

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
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Hyperlink in excel opens email...can a macro fill in and insert attachments? 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

Other Forums: Access Forums

All times are GMT -7. The time now is 04:12 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft