Auto-create receipt from email and forward to payer

Status
Not open for further replies.

ramjet

New Member
Outlook version
Outlook 2013 64 bit
Email Account
Exchange Server
The organisation I work for has a PayPal account to accept payments from customers. When a payment has been made, PayPal sends us an email with details of the customer's name, email address, amount paid etc.

We would like to do the following;

1. From the PayPal message, extract the transaction date, customer's name, email address, item purchased, item number and amount paid.

2. The details extracted are then pasted into a template to form a receipt cum invoice. The template will have a logo, possibly a "PAID" watermark and certain text required to meet local tax laws.

3. The receipt/invoice is then saved and emailed to the customer.

We would want this all to happen automatically ie upon receipt of the PayPal email.

Would this be possible, and if so, could you please suggest a source for suitable and adaptable scripts? I found this on your site but I think only a small part of it would be suitable;

http://www.slipstick.com/developer/run-a-script-rule-autoreply-using-a-template/

Though I have some experience setting up macros in Excel, I am prepared to give it a go.
 

Diane Poremsky

Senior Member
Outlook version
Outlook 2016 32 bit
Email Account
Office 365 Exchange
Yes, it's possible. Are you using an attached document or an email as the receipt?
You need to use a regex to grab the values and can use bookmarks in the template to insert them.

I receive reminders from a google calendar and use a regex to grab the information from the email and send it to bookmarks in word to create a pdf invoice. I have to select the reminder and add the pdf to the email message which it creates.

Regex: http://www.slipstick.com/developer/regex-parse-message-text/
Bookmark sample: http://www.slipstick.com/developer/create-letter-contact-vba/

This is the regex i use to get values from an email and save to a global variable. It could easily be converted to a script to use in a rule. If you get multiple receipt messages at once, you'll probably need to have all the code in one macro and not use global values.

Code:
    Dim strSubject, strNote, strName, strTo, strDate, strFirst As String 
 
Sub InvoiceGoogleInvite() 
    Dim olMail As Outlook.MailItem 
    Dim Reg1 As RegExp 
    Dim M1 As MatchCollection 
    Dim M As Match 
    Dim strResult(4) As String 
    Dim strTest(4) As String 
       
   Set olMail = Application.ActiveExplorer().Selection(1)
  If InStr(1, olMail.To, "Google Calendar") > 0 Then 
       MsgBox ("Not a Google Calendar Reminder") 
       Exit Sub 
    End If 
   
   Set Reg1 = New RegExp 
  
 
For i = 1 To 4 
strResult(i) = "" 
With Reg1 
    Select Case i 
    Case 1 
        .Pattern = "(Attendees\s*[:]\s*(.*)\((.*[@].*)\)\s*)\n\s*" 
        .Global = False 
      
   Case 2 
       .Pattern = "(Note from (.*)[:]\s*(.*))\n" 
       .Global = False 
  
   Case 3 
       .Pattern = "(When\s*((.*)(.*)[Eastern]))\s*\n" 
       .Global = False 
    Case 4 
        .Pattern = "(-\s*(.*)(.*))\n" 
        .Global = False 
   
   End Select 
   
 
End With 
  
    If Reg1.test(olMail.Body) Then 
   
       Set M1 = Reg1.Execute(olMail.Body) 
        For Each M In M1 
            strResult(i) = M.SubMatches(1) 
            strTest(i) = M.SubMatches(2) 
         
     
        strName = Replace(strResult(1), vbTab, "") ' Trim(strResult(1)) 
         strTo = Replace(strTest(1), vbTab, "") ' Trim(strTest(1)) 
         strNote = Replace(strTest(2), vbTab, "") ' Trim(strTest(2)) 
         strDate = Replace(strResult(3), vbTab, "") ' Trim(strResult(3)) 
         strSubject = Replace(strResult(4), vbTab, "") ' Trim(strResult(4)) 
        'Replace(strResult(2), vbTab, "") 
         Next 
    End If 
         
 
Next i 
 
Dim nn, mm As Variant 
 
nn = InStr(1, strName, " ") 
strFirst = Left(strName, nn) 
mm = InStr(1, strTo, "<") 
strTo = Left(strTo, mm - 2) 
 
Debug.Print strName 
Debug.Print strTo 
Debug.Print strNote 
Debug.Print strDate 
Debug.Print strSubject 
 
Set Reg1 = Nothing 
 
' pass the values to a macro to create invoices 
SendMeetingInvoice 
 
End Sub
 

ramjet

New Member
Outlook version
Outlook 2013 64 bit
Email Account
Exchange Server
Thanks Diane.

This gives me something to work with, though it looks pretty daunting.

After I posted my initial query, I played around with a PayPal email message by copying the whole body and pasted it into an Excel spreadsheet. A second spreadsheet referencing the cells in the first, along with a logo and other text, gave me an acceptable form of receipt/invoice to send as an attachment.

I wanted to record a macro in Outlook but there doesn't seem a way to do this. The macro would copy the email body, open the Excel workbook, paste the data, save the workbook under a new name and email the receipt/invoice.

Do you think this would be easier to implement, and if so, what would the code be to copy and paste the data?

In the meantime, I'll try and interpret the script you have kindly given and see how far I get.

Thanks again.
 

Diane Poremsky

Senior Member
Outlook version
Outlook 2016 32 bit
Email Account
Office 365 Exchange
Outlook doesn't have a macro recorder - you can use word and tweak the code to work in outlook.

I didn't have a chance today to write up an invoicing article, but it should work for you - sending a pdf is better than sending a spreadsheet (which is more easily edited). Word can save as a pdf. Or you may be able to do the invoice in an email, no attachment.
 
Status
Not open for further replies.
Thread starter Similar threads Forum Replies Date
rc4524 Create auto follow-up reminder email for already sent messages Outlook VBA and Custom Forms 1
S Auto Create new Task upon Current Task completion Outlook VBA and Custom Forms 28
C Visual Basic auto create task from email including attachments Using Outlook 9
R Auto create of an Outlook distribution list from a Contacts sub-folder Using Outlook 3
D create auto number field in task form Outlook VBA and Custom Forms 4
H Create/Apply auto formatting rules by VB? Outlook VBA and Custom Forms 2
G Macro: Create New Message and Auto populate To Field Outlook VBA and Custom Forms 5
S Create Auto eMail with Web table Content Outlook VBA and Custom Forms 1
S Outlook Macro to send auto acknowledge mail only to new mails received to a specific shared inbox Outlook VBA and Custom Forms 0
V Auto-Submitted: auto-replied in header Using Outlook 0
R Auto display of new email does not work on non-default account Outlook VBA and Custom Forms 0
B Outlook 2016 Auto-archive creates new folder Using Outlook 4
J Edit auto-complete list in Outlook 2016+/365? Using Outlook 0
P Auto assign shared mailbox Outlook VBA and Custom Forms 1
M Outlook 2010 Problem with OutLook 2010 32 bit, after Windows Auto Update Using Outlook 3
P [SOLVED] Auto remove [EXTERNAL] from subject Using Outlook 14
Z Add text to auto-forwarded e-mail Outlook VBA and Custom Forms 4
N Disable Auto Read Receipts sent after using Advanced Find Using Outlook 4
Q Prompt button to auto turn on Out of Office Outlook VBA and Custom Forms 3
P Auto Insert Current Date or Time into Email Subject Outlook VBA and Custom Forms 2
S Messages moved / deleted by auto-archive are not synchronized to exchange Exchange Server Administration 8
B Outlook 2010 is Auto Purging when not configured for that Using Outlook 1
M VBA to auto forward message with new subject and body text Outlook VBA and Custom Forms 8
A Auto Accept Meetings from the General Calendar Using Outlook 3
R auto send email when meeting closes from a shared calendar only Outlook VBA and Custom Forms 2
S auto-mapping mailboxes in outlook impacting an ost file? Exchange Server Administration 2
M Auto expand Distribution List Before Sending Email Outlook VBA and Custom Forms 1
M Auto-export mail to Excel Outlook VBA and Custom Forms 2
Ms_Cynic Auto-pasting email content in calendar appt? Using Outlook 2
R How Do I insert images in and Auto Reply Using Outlook 3
S Received mail as part of DL, need to auto-CC the same when replying Outlook VBA and Custom Forms 5
T Have Outlook 2016 suggest email address auto complete entries directly from the user's contacts list Using Outlook 10
T Have Outlook 2016 suggest email address auto complete entries directly from the user's contacts list Using Outlook 0
P Auto scroll to specific folder in Folder Pane Outlook VBA and Custom Forms 3
C Auto categorize duplicate subjects Outlook VBA and Custom Forms 11
N Auto-complete - block select emails Using Outlook 3
C Auto save outlook attachments when email is received Outlook VBA and Custom Forms 1
J HELP- Rule to auto strip prepend from external emails Using Outlook 0
S BCM Auto Backup Data and Customizations BCM (Business Contact Manager) 6
G Auto accept meeting request for non primary account Outlook VBA and Custom Forms 1
J Outlook Rules - Changing auto-submit address in multiple rules, according to rule name Outlook VBA and Custom Forms 0
E Outlook Form - Voting Responses Not Auto Processing If Form Contains Any Code Outlook VBA and Custom Forms 0
J Auto Forward - Include Attachment and change Subject depending on original sender Outlook VBA and Custom Forms 3
K Extract email address from body and auto-reply outlook Using Outlook 1
S Auto move search results to folder Outlook VBA and Custom Forms 0
E Outlook 2010 disable date auto-complete Using Outlook 2
C Auto subject,name,email,deferred Using Outlook 2
ashcosta2 Auto Reply rule based on speficied time Outlook VBA and Custom Forms 0
B Auto Preview Attachment in Inspector Reading Pane Outlook VBA and Custom Forms 1
Z Auto Forward Using Outlook 4
Similar threads


















































Top