Solved

Macro for replying to Emails in Outlook

Posted on 2010-08-27
19
196 Views
Last Modified: 2012-05-10
Hi all,

I receive emails with shipping information from suppliers and would like to take some of the information and create a macro that formats the data to send an email to a customer.

So... the email I get from the supplier contains the lines:


Customer Name: John Smith

Email: johnsmith@sdd.com

Shipping Company: UPS

Tracking Number: 353453222

Estimated Arrival Date: 3rd September 2010

When I execute the macro I would like it to create an email to the email address from the line "Email :" above.

Then I would like it to say "Dear" and then the first name from the Customer Name line.

Then I'll add some text and would also like to include all the of information above (Shipping company, tracking number and arrival date)

Thanks so much for your help,

D



0
Comment
Question by:TLN_CANADA
  • 9
  • 6
  • 4
19 Comments
 
LVL 33

Expert Comment

by:digitap
ID: 33549997
How automated do you want this macro to be?
0
 

Author Comment

by:TLN_CANADA
ID: 33551760
Hi digitap,

I would like the macro to preform the tasks I mentioned above to generate as an email to the customer that looks something like this.

Dear John,

Your order has now been shipped by {UPS} and the tracking number is {43434343} and it will arrive on {date from email}

Regards,


Then I can review it and press send.


Thanks for your help,

Derek
0
 
LVL 33

Expert Comment

by:digitap
ID: 33551785
To establish expectation, I won't get a chance to look at this until Monday...unless, of course, someone else comes up with a solution before me...>GRIN<!
0
 

Author Comment

by:TLN_CANADA
ID: 33551814
Thanks for letting me know digitap, have a good weekend!
0
 
LVL 33

Expert Comment

by:digitap
ID: 33551821
u2...sleep.
0
 
LVL 59

Expert Comment

by:Chris Bottomley
ID: 33552148
How about:

Note I have added a line for a dummy subject ... slightly obvious as lots of hash symbols ... you can edit or delete as suits you

Chris
Sub Q_26435925()

Dim para As Variant

Dim strArray() As String

Dim mai As Object

Dim strSearchFor() As Variant: strSearchFor = Array("Email:", "Shipping Company:", "Tracking Number:", "Estimated Arrival Date:", "Customer Name:")

Dim strResults() As String

Dim elem As Integer

Const str2 As String = ""

Const str3 As String = ""

Const str4 As String = ""

    

    ReDim strResults(UBound(strSearchFor))

    If TypeName(Application.ActiveWindow) = "Explorer" Then

        Set mai = Application.ActiveExplorer.Selection.item(1)

    ElseIf TypeName(Application.ActiveWindow) = "Inspector" Then

            Set mai = Application.ActiveInspector.CurrentItem

    Else

        Exit Sub

    End If

    With mai

        strArray = Split(.body, vbCrLf)

        For Each para In strArray

            If para <> "" Then

                For elem = LBound(strSearchFor) To UBound(strSearchFor)

                    If LCase(Left(para, Len(strSearchFor(elem)))) = LCase(strSearchFor(elem)) Then strResults(elem) = Trim(Split(para, ":")(1))

                Next

            End If

        Next

    End With

    Set mai = Nothing

    If strResults(0) = "" Then Exit Sub

    ' Only proceed if we have an email address!

    Set mai = Application.CreateItem(olMailItem)

    With mai

        .To = strResults(0)

        .Subject = "############################"

        .body = "Dear " & Split(strResults(4) & " ", " ")(0) & vbCrLf & vbCrLf & _

            "In regard to:" & vbCrLf & _

            "     Shipping Company: " & strResults(4) & vbCrLf & _

            "     Tracking Number: " & strResults(2) & vbCrLf & _

            "     Arrival Date: " & strResults(3) & vbCrLf & vbCrLf

        .Display

    End With



End Sub

Open in new window

0
 

Author Comment

by:TLN_CANADA
ID: 33553907
Thanks Chris,

This is working great now. There are a couple of small changes to the code though that I am unable to perform.

1. In the Email Subject, I would like the company name to appear. Should look like:

TLN Group - {Company Name from strResults(5)}  Your Order has now been shipped by UPS delivery

2. I would like the "Your order has now been shipped ..." line and the "The tracking number for this order " lines to not have a line break between them and also have full stops at the end of each of these lines.

3. Finally, for the Dear and then customer first name, I would like to have a comma after the customers first name.

Thanks again for your help with this,

Derek


Sub Q_26435925()
Dim para As Variant
Dim strArray() As String
Dim mai As Object
Dim strSearchFor() As Variant: strSearchFor = Array("Email:", "Shipping Company:", "Tracking Number:", "Estimated Arrival Date:", "Contact:", "Company Name:")
Dim strResults() As String
Dim elem As Integer
Const str2 As String = ""
Const str3 As String = ""
Const str4 As String = ""
    
    ReDim strResults(UBound(strSearchFor))
    If TypeName(Application.ActiveWindow) = "Explorer" Then
        Set mai = Application.ActiveExplorer.Selection.Item(1)
    ElseIf TypeName(Application.ActiveWindow) = "Inspector" Then
            Set mai = Application.ActiveInspector.CurrentItem
    Else
        Exit Sub
    End If
    With mai
        strArray = Split(.Body, vbCrLf)
        For Each para In strArray
            If para <> "" Then
                For elem = LBound(strSearchFor) To UBound(strSearchFor)
                    If LCase(Left(para, Len(strSearchFor(elem)))) = LCase(strSearchFor(elem)) Then strResults(elem) = Trim(Split(para, ":")(1))
                Next
            End If
        Next
    End With
    Set mai = Nothing
    If strResults(0) = "" Then Exit Sub
    ' Only proceed if we have an email address!
    Set mai = Application.CreateItem(olMailItem)
    With mai
        .To = strResults(0)
        .Subject = "TLN Group - Your Order has now been shipped by UPS delivery strResults(5)"
        .Body = "Dear " & Split(strResults(4) & " ", " ")(0) & vbCrLf & vbCrLf & _
            "Your order has now been shipped with UPS and is due on to arrive on " & strResults(3) & vbCrLf & _
            "The tracking number for this order is " & strResults(2) & vbCrLf & vbCrLf & _
            "To check the status of this delivery: " & vbCrLf & vbCrLf & _
            "1.  Go to http://www.ups.com/tracking/tracking.html" & vbCrLf & _
            "2.  Enter the tracking number above in the Tracking or InfoNotice Numbers Box" & vbCrLf & _
            "3.  Press the Track Button" & vbCrLf & vbCrLf & _
            "We would appreciate it if you could let us know when these goods have been delivered to you." & vbCrLf & vbCrLf & _
            "Best regards," & vbCrLf & _
                   "Derek" & vbCrLf & vbCrLf & _
                   
        .Display
    End With

End Sub

Open in new window

0
 

Author Comment

by:TLN_CANADA
ID: 33553930
Hi Chris,

One other question with this. I want to add this macro to the macro dropdown list you built for me a couple of months ago.

Here is some of the code for it but I'm having difficulty adding it correctly:

Case "test 7"
                    editseven mai
                Case "test 8"
                    editeight mai
                Case "test 9"
                    editnine mai
                Case "test 10"
                    editten mai

(I want this macro to be added to Case "test 10" I have renamed the macro to: Sub editten()

Then on the ThisOurlookSession, I call it:

Set objButton = .Controls.Add(msoControlButton)
                With objButton
                    .Style = msoButtonIconAndCaption
                    .Caption = "Thank Customer for Artwork Confirm"
                    .FaceId = 355
                    .OnAction = "TestSub1"
                    .Parameter = "Test 9"
                End With
                Set objButton = .Controls.Add(msoControlButton)
                With objButton
                    .Style = msoButtonIconAndCaption
                    .Caption = "UPS"
                    .FaceId = 355
                    .OnAction = "TestSub1"
                    .Parameter = "Test 10"
                End With

Please let me know what I need to adjust with this to integrate this.

Many thanks,

Derek


0
 

Author Comment

by:TLN_CANADA
ID: 33554058
Hi Chirs,

I setup another questions with a couple of other queries I have regarding this macro in a slight variation when you have some time.

http://www.experts-exchange.com/Software/Office_Productivity/Groupware/Outlook/Q_26437465.html

Thanks again,

Derek
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 59

Expert Comment

by:Chris Bottomley
ID: 33554304
How about?

Chris
Sub Q_26435925()

Dim para As Variant

Dim strArray() As String

Dim mai As Object

Dim strSearchFor() As Variant: strSearchFor = Array("Email:", "Shipping Company:", "Tracking Number:", "Estimated Arrival Date:", "Contact:", "Company Name:")

Dim strResults() As String

Dim elem As Integer

Const str2 As String = ""

Const str3 As String = ""

Const str4 As String = ""

    

    ReDim strResults(UBound(strSearchFor))

    If TypeName(Application.ActiveWindow) = "Explorer" Then

        Set mai = Application.ActiveExplorer.Selection.item(1)

    ElseIf TypeName(Application.ActiveWindow) = "Inspector" Then

            Set mai = Application.ActiveInspector.CurrentItem

    Else

        Exit Sub

    End If

    With mai

        strArray = Split(.body, vbCrLf)

        For Each para In strArray

            If para <> "" Then

                For elem = LBound(strSearchFor) To UBound(strSearchFor)

                    If LCase(Left(para, Len(strSearchFor(elem)))) = LCase(strSearchFor(elem)) Then strResults(elem) = Trim(Split(para, ":")(1))

                Next

            End If

        Next

    End With

    Set mai = Nothing

    If strResults(0) = "" Then Exit Sub

    ' Only proceed if we have an email address!

    Set mai = Application.CreateItem(olMailItem)

    With mai

        .To = strResults(0)

        .Subject = "TLN Group - " & strResults(5) & " Your Order has now been shipped by UPS delivery"

        .body = "Dear " & Split(strResults(4) & " ", " ")(0) & "," & vbCrLf & vbCrLf & _

            "Your order has now been shipped with UPS and is due on to arrive on " & strResults(3) & ".  " & _

            "The tracking number for this order is " & strResults(2) & "." & vbCrLf & vbCrLf & _

            "To check the status of this delivery: " & vbCrLf & vbCrLf & _

            "1.  Go to http://www.ups.com/tracking/tracking.html" & vbCrLf & _

            "2.  Enter the tracking number above in the Tracking or InfoNotice Numbers Box" & vbCrLf & _

            "3.  Press the Track Button" & vbCrLf & vbCrLf & _

            "We would appreciate it if you could let us know when these goods have been delivered to you." & vbCrLf & vbCrLf & _

            "Best regards," & vbCrLf & _

                   "Derek" & vbCrLf & vbCrLf



        .Display

    End With



End Sub

Open in new window

0
 

Author Comment

by:TLN_CANADA
ID: 33554418
Thanks Chris, this works great! Just need to integrate it into the drop-down you create before now and we'll all done. Let me know if you need any other details with this.

Derek
0
 
LVL 59

Expert Comment

by:Chris Bottomley
ID: 33554436
Probably something like ...
Sub editten(mai as mailitem)

Dim para As Variant

Dim strArray() As String

Dim mai As Object

Dim strSearchFor() As Variant: strSearchFor = Array("Email:", "Shipping Company:", "Tracking Number:", "Estimated Arrival Date:", "Contact:", "Company Name:")

Dim strResults() As String

Dim elem As Integer

Const str2 As String = ""

Const str3 As String = ""

Const str4 As String = ""

    

    ReDim strResults(UBound(strSearchFor))

'    If TypeName(Application.ActiveWindow) = "Explorer" Then

'        Set mai = Application.ActiveExplorer.Selection.item(1)

'    ElseIf TypeName(Application.ActiveWindow) = "Inspector" Then

'            Set mai = Application.ActiveInspector.CurrentItem

'    Else

'        Exit Sub

'    End If

    With mai

        strArray = Split(.body, vbCrLf)

        For Each para In strArray

            If para <> "" Then

                For elem = LBound(strSearchFor) To UBound(strSearchFor)

                    If LCase(Left(para, Len(strSearchFor(elem)))) = LCase(strSearchFor(elem)) Then strResults(elem) = Trim(Split(para, ":")(1))

                Next

            End If

        Next

    End With

    Set mai = Nothing

    If strResults(0) = "" Then Exit Sub

    ' Only proceed if we have an email address!

    Set mai = Application.CreateItem(olMailItem)

    With mai

        .To = strResults(0)

        .Subject = "TLN Group - " & strResults(5) & " Your Order has now been shipped by UPS delivery"

        .body = "Dear " & Split(strResults(4) & " ", " ")(0) & "," & vbCrLf & vbCrLf & _

            "Your order has now been shipped with UPS and is due on to arrive on " & strResults(3) & ".  " & _

            "The tracking number for this order is " & strResults(2) & "." & vbCrLf & vbCrLf & _

            "To check the status of this delivery: " & vbCrLf & vbCrLf & _

            "1.  Go to http://www.ups.com/tracking/tracking.html" & vbCrLf & _

            "2.  Enter the tracking number above in the Tracking or InfoNotice Numbers Box" & vbCrLf & _

            "3.  Press the Track Button" & vbCrLf & vbCrLf & _

            "We would appreciate it if you could let us know when these goods have been delivered to you." & vbCrLf & vbCrLf & _

            "Best regards," & vbCrLf & _

                   "Derek" & vbCrLf & vbCrLf



        .Display

    End With



End Sub

Open in new window

0
 
LVL 59

Expert Comment

by:Chris Bottomley
ID: 33555959
Loo0king at it onscreen it needs a coiuple of tweaks still toi work in the menu environment.

Chris
Sub editten(mai as mailitem)

Dim para As Variant

Dim strArray() As String

Dim nuMai As Object

Dim strSearchFor() As Variant: strSearchFor = Array("Email:", "Shipping Company:", "Tracking Number:", "Estimated Arrival Date:", "Contact:", "Company Name:")

Dim strResults() As String

Dim elem As Integer

Const str2 As String = ""

Const str3 As String = ""

Const str4 As String = ""

    

    ReDim strResults(UBound(strSearchFor))

'    If TypeName(Application.ActiveWindow) = "Explorer" Then

'        Set mai = Application.ActiveExplorer.Selection.item(1)

'    ElseIf TypeName(Application.ActiveWindow) = "Inspector" Then

'            Set mai = Application.ActiveInspector.CurrentItem

'    Else

'        Exit Sub

'    End If

    With mai

        strArray = Split(.body, vbCrLf)

        For Each para In strArray

            If para <> "" Then

                For elem = LBound(strSearchFor) To UBound(strSearchFor)

                    If LCase(Left(para, Len(strSearchFor(elem)))) = LCase(strSearchFor(elem)) Then strResults(elem) = Trim(Split(para, ":")(1))

                Next

            End If

        Next

    End With

'    Set mai = Nothing

    If strResults(0) = "" Then Exit Sub

    ' Only proceed if we have an email address!

    Set numai = Application.CreateItem(olMailItem)

    With numai

        .To = strResults(0)

        .Subject = "TLN Group - " & strResults(5) & " Your Order has now been shipped by UPS delivery"

        .body = "Dear " & Split(strResults(4) & " ", " ")(0) & "," & vbCrLf & vbCrLf & _

            "Your order has now been shipped with UPS and is due on to arrive on " & strResults(3) & ".  " & _

            "The tracking number for this order is " & strResults(2) & "." & vbCrLf & vbCrLf & _

            "To check the status of this delivery: " & vbCrLf & vbCrLf & _

            "1.  Go to http://www.ups.com/tracking/tracking.html" & vbCrLf & _

            "2.  Enter the tracking number above in the Tracking or InfoNotice Numbers Box" & vbCrLf & _

            "3.  Press the Track Button" & vbCrLf & vbCrLf & _

            "We would appreciate it if you could let us know when these goods have been delivered to you." & vbCrLf & vbCrLf & _

            "Best regards," & vbCrLf & _

                   "Derek" & vbCrLf & vbCrLf



        .Display

    End With



End Sub

Open in new window

0
 
LVL 33

Expert Comment

by:digitap
ID: 33557746
hey, glad things worked out and you got your macro.
0
 

Author Comment

by:TLN_CANADA
ID: 33563449
Hi Chris,

So.. I've added your code above and also changed the parameters in other places to:

Dim param As String

    param = LCase(Application.ActiveExplorer.CommandBars.ActionControl.Control.Parameter)
    For Each mai In Application.ActiveExplorer.Selection
        If mai.Class = olMail Then
            Select Case param
                Case "test 1"
                    editone mai
                Case "test 2"
                    edittwo mai
                Case "test 3"
                    editthree mai
                Case "test 4"
                    editfour mai
                Case "test 5"
                    editfive mai
                Case "test 6"
                    editsix mai
                Case "test 7"
                    editseven mai
                Case "test 8"
                    editeight mai
                Case "test 9"
                    editnine mai
                Case "test 10"
                    editten mai
                Case Else
                    MsgBox "OOPS"
            End Select
        End If
    Next
End Sub

Private Sub Application_ItemContextMenuDisplay(ByVal CommandBar As Office.CommandBar, ByVal Selection As Selection)
Const msoButtonIconAndCaption = 3
Dim objButton As Object
Dim objMenu As Object
     
            Set objMenu = CommandBar.Controls.Add(Type:=msoControlPopup)
            objMenu.Caption = "&Responses"
            With objMenu
                Set objButton = .Controls.Add(msoControlButton)
                With objButton
                    .Style = msoButtonIconAndCaption
                    .Caption = "First Sample Produced"
                    .FaceId = 355
                    .OnAction = "TestSub1"
                    .Parameter = "Test 1"
                End With
                Set objButton = .Controls.Add(msoControlButton)
                With objButton
                    .Style = msoButtonIconAndCaption
                    .Caption = "Sample Confirmation for Supplier"
                    .FaceId = 355
                    .OnAction = "TestSub1"
                    .Parameter = "Test 2"
                End With
                Set objButton = .Controls.Add(msoControlButton)
                With objButton
                    .Style = msoButtonIconAndCaption
                    .Caption = "Paypal Receipt"
                    .FaceId = 355
                    .OnAction = "TestSub1"
                    .Parameter = "Test 3"
                End With
                Set objButton = .Controls.Add(msoControlButton)
                With objButton
                    .Style = msoButtonIconAndCaption
                    .Caption = "Standard Enquiry"
                    .FaceId = 355
                    .OnAction = "TestSub1"
                    .Parameter = "Test 4"
                End With
                Set objButton = .Controls.Add(msoControlButton)
                With objButton
                    .Style = msoButtonIconAndCaption
                    .Caption = "Pantone Chart"
                    .FaceId = 355
                    .OnAction = "TestSub1"
                    .Parameter = "Test 5"
                End With
                Set objButton = .Controls.Add(msoControlButton)
                With objButton
                    .Style = msoButtonIconAndCaption
                    .Caption = "Gift Boxes"
                    .FaceId = 355
                    .OnAction = "TestSub1"
                    .Parameter = "Test 6"
                End With
                Set objButton = .Controls.Add(msoControlButton)
                With objButton
                    .Style = msoButtonIconAndCaption
                    .Caption = "Cheque Receipt"
                    .FaceId = 355
                    .OnAction = "TestSub1"
                    .Parameter = "Test 7"
                End With
                Set objButton = .Controls.Add(msoControlButton)
                With objButton
                    .Style = msoButtonIconAndCaption
                    .Caption = "Sascha Custom Quote"
                    .FaceId = 355
                    .OnAction = "TestSub1"
                    .Parameter = "Test 8"
                End With
                Set objButton = .Controls.Add(msoControlButton)
                With objButton
                    .Style = msoButtonIconAndCaption
                    .Caption = "Thank Customer for Artwork Confirm"
                    .FaceId = 355
                    .OnAction = "TestSub1"
                    .Parameter = "Test 9"
                End With
                Set objButton = .Controls.Add(msoControlButton)
                With objButton
                    .Style = msoButtonIconAndCaption
                    .Caption = "UPS"
                    .FaceId = 355
                    .OnAction = "TestSub1"
                    .Parameter = "Test 10"
                End With
            End With
End Sub

It gives the error:

By Ref Argument Type Mismatch and points to the line:

editten mai


Thanks,

Derek
0
 
LVL 59

Expert Comment

by:Chris Bottomley
ID: 33564287
Where did you save the editten macro, (http:#33555959)?  It should be in a normal code module.

Chris
0
 

Author Comment

by:TLN_CANADA
ID: 33564461
It is in Module one, the same as the other sections of this part of the code (e.g. editnine) that work properly.
0
 
LVL 59

Accepted Solution

by:
Chris Bottomley earned 500 total points
ID: 33564683
How about changing editten as follows:

Chris
Sub editten(mai as variant)

Dim para As Variant

Dim strArray() As String

Dim nuMai As Object

Dim strSearchFor() As Variant: strSearchFor = Array("Email:", "Shipping Company:", "Tracking Number:", "Estimated Arrival Date:", "Contact:", "Company Name:")

Dim strResults() As String

Dim elem As Integer

Const str2 As String = ""

Const str3 As String = ""

Const str4 As String = ""

    

    ReDim strResults(UBound(strSearchFor))

'    If TypeName(Application.ActiveWindow) = "Explorer" Then

'        Set mai = Application.ActiveExplorer.Selection.item(1)

'    ElseIf TypeName(Application.ActiveWindow) = "Inspector" Then

'            Set mai = Application.ActiveInspector.CurrentItem

'    Else

'        Exit Sub

'    End If

    With mai

        strArray = Split(.body, vbCrLf)

        For Each para In strArray

            If para <> "" Then

                For elem = LBound(strSearchFor) To UBound(strSearchFor)

                    If LCase(Left(para, Len(strSearchFor(elem)))) = LCase(strSearchFor(elem)) Then strResults(elem) = Trim(Split(para, ":")(1))

                Next

            End If

        Next

    End With

'    Set mai = Nothing

    If strResults(0) = "" Then Exit Sub

    ' Only proceed if we have an email address!

    Set numai = Application.CreateItem(olMailItem)

    With numai

        .To = strResults(0)

        .Subject = "TLN Group - " & strResults(5) & " Your Order has now been shipped by UPS delivery"

        .body = "Dear " & Split(strResults(4) & " ", " ")(0) & "," & vbCrLf & vbCrLf & _

            "Your order has now been shipped with UPS and is due on to arrive on " & strResults(3) & ".  " & _

            "The tracking number for this order is " & strResults(2) & "." & vbCrLf & vbCrLf & _

            "To check the status of this delivery: " & vbCrLf & vbCrLf & _

            "1.  Go to http://www.ups.com/tracking/tracking.html" & vbCrLf & _

            "2.  Enter the tracking number above in the Tracking or InfoNotice Numbers Box" & vbCrLf & _

            "3.  Press the Track Button" & vbCrLf & vbCrLf & _

            "We would appreciate it if you could let us know when these goods have been delivered to you." & vbCrLf & vbCrLf & _

            "Best regards," & vbCrLf & _

                   "Derek" & vbCrLf & vbCrLf



        .Display

    End With



End Sub

Open in new window

0
 

Author Comment

by:TLN_CANADA
ID: 33599964
Thanks Chris! Here is one more I have, a slight variation of this one.

http://www.experts-exchange.com/Software/Office_Productivity/Groupware/Outlook/Q_26451142.html

0

Featured Post

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

Check out this infographic on what you need to make a good email signature that will work perfectly for your organization.
Outlook Free & Paid Tools
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
This Experts Exchange video Micro Tutorial shows how to tell Microsoft Office that a word is NOT spelled correctly. Microsoft Office has a built-in, main dictionary that is shared by Office apps, including Excel, Outlook, PowerPoint, and Word. When …

757 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now