View Single Post
 
Old 09-16-2014, 10:36 PM
gmayor's Avatar
gmayor gmayor is offline Windows 7 64bit Office 2010 32bit
Expert
 
Join Date: Aug 2014
Posts: 4,142
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 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