automatic reply with parametric Excel file attached

gmayor · #4 12-06-2017, 05:38 AM

Without access to the workbook, I can only point you in the right direction. The process itself is simple enough. You will need a function in your workbook that takes the ID from the e-mail, creates the PDF and returns the PDF path and name. The basics are

Code:

Function MacroName(strID As String) As String
    'Run your workbook code here
    MacroName = "C:\path\" & strID & ".pdf" ' return the PDF path and name
End Function

In Outlook, you need a rule to identify the messages that require the reply and then run the script xlGetData to process those messages as they arrive e.g. as follows.

Change the various parts to reflect the true names of your workbook and the function. If you are comfortable with VBA, you should be able to make this work with your workbook. It tests OK with the examples used.

Code:

Option Explicit

Public Sub xlGetData(olItem As MailItem)
Const strWorkBook As String = "C:\Path\Workbookname.xlsm" 'The name of the workbook
Dim xlApp As Object
Dim xlWB As Object
Dim strID As String
Dim strPDF As String
Dim bStarted As Boolean
Dim olReply As MailItem
Dim fso As Object
    strID = olItem.Subject
    On Error Resume Next
    Set xlApp = GetObject(, "Excel.Application")
    If Err <> 0 Then
        Set xlApp = CreateObject("Excel.Application")
        bStarted = True
    End If
    On Error GoTo 0
    Set xlWB = xlApp.workbooks.Open(strWorkBook)
    strPDF = xlApp.Run("'" & xlWB.Name & "'!MacroName", strID)

    Set fso = CreateObject("Scripting.FileSystemObject")
    If fso.FileExists(strPDF) Then
        Set olReply = olItem.Reply
        With olReply
            .Attachments.Add strPDF
            .Display
            '.Send 'Restore this line after testing
        End With
Debug.Print strID & vbTab & strPDF
    Else
        MsgBox "The file" & vbCr & strPDF & vbCr & "does not exist."
    End With
lbl_Exit:
    xlWB.Close 0
    If bStarted Then
        xlApp.Quit
    End If
        Set xlApp = Nothing
        Set xlWB = Nothing
    Exit Sub
End Sub

You can run the following macro with a selected message to test the process

Code:

Sub TestMacro()
Dim olMsg As MailItem
    On Error Resume Next
    Set olMsg = ActiveExplorer.Selection.Item(1)
    xlGetData olMsg
lbl_Exit:
    Exit Sub
End Sub

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Automatic SMS from Excel file with condition	aspirout	Excel Programming	3	11-02-2019 04:17 AM
auto respond with parametric attachment upon request	tas75	Outlook	0	03-05-2017 11:14 AM
copy data from outlook attached excel file into a local one	s_samira_21	Outlook	4	09-17-2014 11:46 PM
Automatic update of links in template - closing attached template without saving	stefaan	Word	2	11-02-2013 07:46 AM
Automatic reply with variable attachment	subby80	Outlook	0	10-20-2011 06:59 AM