none
Outlook 2010; Tasks to Trigger Different E-mails Sent to Salesforce RRS feed

  • Question

  • I've been searching the internet to try and find a solution to this question, but I haven't been successful.

    Currently, I'm sending an e-mail to one salesperson every morning.  I found VBA Code that sends the e-mail and attachments successfully.  My question is, how can the code be modified to allow more sales people to get their own specific e-mails and attachments, on their own schedule.  (i.e. Sales Person 1 wants the report daily, Sales Person 2 wants it weekly, or every other day, etc.)

    As I am a beginner with VBA Code, please be patient, and if there is additional information needed to answer my question, please let me know.

    The code that I attempted to use is below.  Please keep in mind that this is my attempt to modify the current code in place to add the additional sales person.  I'd like to have flexibility to easily add more as necessary in the future using Outlook Task Reminders.

    Thank you all in advance for any assistance provided.  Searching the forums has been enlightening. Also all of this code is sitting in the: ThisOutLookSession

    Private Sub Application_Reminder(ByVal Item As Object)

    If Item.Subject = "SALESPERSON #1 Sales Report" Then

     'FOLLOWING CODE IS TRIGGERED BY TASK ITEM REMINDER TITLED "SALESPERSON#1 Sales Report"

    Dim objOutlook As Object

    Dim objOutlookMsg As Object

    Set objOutlook = CreateObject("Outlook.Application")

    Set objOutlookMsg = objOutlook.CreateItem(0)

    With objOutlookMsg

        .To = "xxxxxx@xxxxx.com"

        .CC = "xxxxxx@xxxxx.com"

        .Subject = "Automatic Message: {Sales Person #1} Reports for: " & Format(Date, "mm-dd-yy") & " " & Format(Now(), "hh:mm AMPM")

        .Body = ""

        .HTMLBody = "HTML BODY MESSAGE"

        .Attachments.Add ("h:\vpc - sales\access sales reports\report #1 " & Format(Date, "yyyy-mm-dd") & ".pdf")

        .Attachments.Add ("h:\vpc - sales\access sales reports\report #2 " & Format(Date, "yyyy-mm-dd") & ".pdf")

        .Send

        End With

    Set objOutlookMsg = Nothing

    Set objOutlook = Nothing

    ' FOLLOWING CODE REMOVES THE TASK ITEM THAT TRIGGERS THE REPORTS BEING E-MAILED

        Dim objRem As Reminder

        Dim objRems As Reminders

        If Item.Subject = "SALESPERSON#1 Sales Report" Then

            'downloadAndSendSpreadReport

            Set objRems = Application.Reminders

            i = 0

            For Each objRem In objRems

                i = i + 1

                If objRem.Caption = "SALESPERSON#1 Sales Report" Then

                    objRems.Remove i

                    If objRem.IsVisible Then

                        objRem.Dismiss

                    End If

                    Exit For

                End If

            Next objRem

            Item.ReminderSet = False

            Item.Delete

            'Item.Close

        End If

    ' FOLLOWING CODE ESTABLISHES TOMORROW'S TASK

     Dim myItem As Outlook.TaskItem

     

     Dim myDelegate As Outlook.Recipient

     

     Set myItem = Application.CreateItem(olTaskItem)

     

     myItem.Subject = "{Sales Person} Sales Report"

     'myItem.StartDate = Now() + 1

     'myItem.DueDate = Now() + 1

     myItem.ReminderSet = True

     myItem.ReminderTime = Format(Now() + 1, "short date") & " " & Format("6:15:00 AM", "short time")

     myItem.Save

    End If

    ' FOLLOWING CODE LAUNCHES {SALES PERSON #2} E-MAIL REPORTS

    If Item.Subject = "SALES PERSON #2" Then

     'FOLLOWING CODE IS TRIGGERED BY TASK ITEM REMINDER TITLED "SALES PERSON #2 Sales Report"

    Dim objOutlookSALESPERSON#2As Object

    Dim objOutlookMsgSALESPERSON#2 As Object

    Set objOutlookSALESPERSON#2 = CreateObject("Outlook.Application")

    Set objOutlookMsgSALESPERSON#2 = objOutlookSALESPERSON#2.CreateItem(0)

    With objOutlookMsgSALESPERSON#2

        .To = "XXXXXX@XXXXXXXXXXXXX.com"

        '.CC = " XXXXXX@XXXXXXXXXXXXX.com"

        .Subject = "Automatic Message: SALES PERSON #2 Reports for: " & Format(Date, "mm-dd-yy") & " " & Format(Now(), "hh:mm AMPM")

        .Body = ""

        .HTMLBody = "HTML BODY MESSAGE"

        .Attachments.Add ("h:\vpc - sales\access sales reports\MTD Summary Invoice + On Order – SALES PERSON #2 " & Format(Date, "yyyy-mm-dd") & ".pdf")

        .Send

        End With

    Set objOutlookMsgSALESPERSON#2 = Nothing

    Set objOutlookSALESPERSON#2 = Nothing

    ' FOLLOWING CODE REMOVES THE TASK ITEM THAT TRIGGERS THE REPORTS BEING E-MAILED

        Dim objRemSALESPERSON#2 As Reminder

        Dim objRemsSALESPERSON#@ As Reminders

        If Item.Subject = "SALESPERSON #2 Report" Then

            'downloadAndSendSpreadReport

            Set objRemsSALESPERSON#2 = Application.Reminders

            i = 0

            For Each objRemSALESPERSON#2 In objRemsSALESPERSON#2

                i = i + 1

                If objRemSALESPERSON#2.Caption = "SALESPERSON#2" Then

                    ObjRemsSALESPERSON#2.Remove i

                    If objRemSALESPERSON#2.IsVisible Then

                        ObjRemSALESPERSON#2.Dismiss

                    End If

                    Exit For

                End If

            Next objRemSALESPERSON#2

            Item.ReminderSet = False

            Item.Delete

            'Item.Close

        End If

    ' FOLLOWING CODE ESTABLISHES TOMORROW'S TASK

     Dim myItemSALESPERSON#2 As Outlook.TaskItem

     

     Dim myDelegateSALESPERSON#2 As Outlook.Recipient

     

     Set myItemSALESPERSON#2 = Application.CreateItem(olTaskItem)

     

     MyItemSALESPERSON#2.Subject = "SALESPERSON#2 Sales Report"

     'myItem.StartDate = Now() + 1

     'myItem.DueDate = Now() + 1

     MyItemSALESPERSON#2.ReminderSet = True

     MyItemSALESPERSON#2.ReminderTime = Format(Now() + 1, "short date") & " " & Format("6:15:00 AM", "short time")

     MyItemSALESPERSON#2.Save

    End If

    End Sub

    Wednesday, May 8, 2013 6:37 PM

Answers

  • Hi JMDataConsultant,

    I use to vba script to reproduce your scenario:

    CreateTask to create the specific task. SendMail to send mail.

    Option Explicit
     
    Sub CreateTask()
        Dim Item As Outlook.TaskItem
        Dim strAddress As String
        Dim addMore As Boolean
        Dim btnResult As Integer
        
        Set Item = Application.CreateItem(olTaskItem)
        
        addMore = True
        
        While (addMore)
            strAddress = InputBox(Prompt:="Email Address:", Title:="Add Recipients")
            btnResult = MsgBox(Prompt:="Do you want add another address?", Buttons:=vbYesNoCancel)
            If strAddress <> "" Then
                Item.Recipients.Add (strAddress)
            End If
            If Not btnResult = vbYes Then
                addMore = False
            End If
        Wend
        
        Item.Recipients.ResolveAll
        
        Item.Display
        
        Set Item = Nothing
    End Sub
    
    Sub SendMail()
        Dim TF As MAPIFolder
        Dim Item As TaskItem
        Dim mail As MailItem
        Dim Recipient As Recipient
        Dim Attachment As Attachment
        
        Set TF = Application.Session.GetDefaultFolder(olFolderTasks)
        Set mail = Application.CreateItem(olMailItem)
        
        For Each Item In TF.Items
            If Not Item.Complete And Item.DueDate = Date Then
                For Each Recipient In Item.Recipients
                    mail.Recipients.Add (Recipient)
                Next
                mail.Recipients.ResolveAll
                For Each Attachment In Item.Attachments
                    Attachment.SaveAsFile ("C:\Users\****\AppData\Local\Temp\" + Attachment.FileName)
                    mail.Attachments.Add ("C:\Users\****\AppData\Local\Temp\" + Attachment.FileName)
                Next
                'To-Do do other operation here
                mail.Display
                Item.Delete
            End If
        Next
        
        Set Item = Nothing
        Set mail = Nothing
        Set Recipient = Nothing
        Set Attachment = Nothing
        Set TF = Nothing
    End Sub

    I hope it can help you. Now let's other issues :) (I noticed this is the only thread you post via this account. Which other thread you want to discuss?)

    Have a good day,

    Tom



    Monday, May 13, 2013 3:31 AM
    Moderator
  • Tom,

    Thanks to your coding and the ideas it gave me, I create Sub Functions (if that's the proper terminology) for each sales person, and I'll be creating one general task to E-Mail Sales Reports, and then just create Subs for any additional Sales People.

    While I still think coming up with a macro that could prompt NON-VBA Users a method of creating the tasks and telling what files would need to be attached would be useful, this solution will accomplish what I was trying to do.

    Thanks for your time and efforts with this.

    JMDataConsultant

    Wednesday, May 15, 2013 2:23 PM

All replies

  • Hi JMDataConsultant

    I suggest your map the target person and attachment to a task. when you want to perform it. use your program iterate your task folder and export the recipient and attachment from the task to the mail.

    Have a good day,

    Tom

    Thursday, May 9, 2013 5:37 AM
    Moderator
  • Hi Tom,

    Thanks for your reply. I had attempted to create a separate Task for the additional sales person, but the VBA Code didn't trigger.  There were errors that had to be debugged, and as I'm new to VBA, I had no clue what the problem was.

    I don't know how I would automatically put the attached files to a task, as the file name changes daily based on the date that the report is being generated.

    Thanks for the suggestion.

    JMDataConsultant

    Thursday, May 9, 2013 1:10 PM
  • Hi JMDataConsultant,

    OK, Let's see whether I can provide your VBA script. However I have some important issues to do today. I will come back with my script soon.

    Thanks for your patience.

    Have a good day,

    Tom

    Friday, May 10, 2013 1:10 AM
    Moderator
  • Hi Tom,

    Any guidance you could provide would be greatly appreciated.  Please take care of your other issues. This is not an urgent project for me.

    Thanks.

    JMDataConsultant

    Friday, May 10, 2013 6:33 PM
  • Hi JMDataConsultant,

    I use to vba script to reproduce your scenario:

    CreateTask to create the specific task. SendMail to send mail.

    Option Explicit
     
    Sub CreateTask()
        Dim Item As Outlook.TaskItem
        Dim strAddress As String
        Dim addMore As Boolean
        Dim btnResult As Integer
        
        Set Item = Application.CreateItem(olTaskItem)
        
        addMore = True
        
        While (addMore)
            strAddress = InputBox(Prompt:="Email Address:", Title:="Add Recipients")
            btnResult = MsgBox(Prompt:="Do you want add another address?", Buttons:=vbYesNoCancel)
            If strAddress <> "" Then
                Item.Recipients.Add (strAddress)
            End If
            If Not btnResult = vbYes Then
                addMore = False
            End If
        Wend
        
        Item.Recipients.ResolveAll
        
        Item.Display
        
        Set Item = Nothing
    End Sub
    
    Sub SendMail()
        Dim TF As MAPIFolder
        Dim Item As TaskItem
        Dim mail As MailItem
        Dim Recipient As Recipient
        Dim Attachment As Attachment
        
        Set TF = Application.Session.GetDefaultFolder(olFolderTasks)
        Set mail = Application.CreateItem(olMailItem)
        
        For Each Item In TF.Items
            If Not Item.Complete And Item.DueDate = Date Then
                For Each Recipient In Item.Recipients
                    mail.Recipients.Add (Recipient)
                Next
                mail.Recipients.ResolveAll
                For Each Attachment In Item.Attachments
                    Attachment.SaveAsFile ("C:\Users\****\AppData\Local\Temp\" + Attachment.FileName)
                    mail.Attachments.Add ("C:\Users\****\AppData\Local\Temp\" + Attachment.FileName)
                Next
                'To-Do do other operation here
                mail.Display
                Item.Delete
            End If
        Next
        
        Set Item = Nothing
        Set mail = Nothing
        Set Recipient = Nothing
        Set Attachment = Nothing
        Set TF = Nothing
    End Sub

    I hope it can help you. Now let's other issues :) (I noticed this is the only thread you post via this account. Which other thread you want to discuss?)

    Have a good day,

    Tom



    Monday, May 13, 2013 3:31 AM
    Moderator
  • Tom,

    Thank you very much for your efforts and time.  I greatly appreciate it.

    There are a few things that I need to clarify as, assuming I'm running the code correctly, it's not fully accomplishing what I want to do.

    1. The file names for each sales person would change daily based on the date as the report is being generated via Microsoft Access with the date it was created as part of the file name.  So the attached files would have to be able to be changed daily.

    2. Once I've created a task for a salesperson, it would need to be regenerated for the next day after the reminder fired and e-mail was sent.

    3. Is there a way when creating the task to prompt for the path and file name up to the part prior to the date?  If you look at the code I submitted on this topic, you'll see how I was attaching the files.  Unless there is another way of doing this, I would think I would need a similar method upon initial input of the task.

    4. I'm looking to have this done automatically without user input in the VBA Code or clicking a Macro.  Again, assuming that I'm running the code correctly, I'm having to click on Macro to Send e-mail, and it only pulls up the form, waiting for more information.

    Looking over the coding that you supplied, it did provide some ideas that I'll be experimenting with.

    If you have further thoughts, suggestions on this, I would definitely appreciate hearing them.

    Again, thanks for your time and efforts.

    JMDataConsultant

    Tuesday, May 14, 2013 7:42 PM
  • Tom,

    Thanks to your coding and the ideas it gave me, I create Sub Functions (if that's the proper terminology) for each sales person, and I'll be creating one general task to E-Mail Sales Reports, and then just create Subs for any additional Sales People.

    While I still think coming up with a macro that could prompt NON-VBA Users a method of creating the tasks and telling what files would need to be attached would be useful, this solution will accomplish what I was trying to do.

    Thanks for your time and efforts with this.

    JMDataConsultant

    Wednesday, May 15, 2013 2:23 PM