Results 1 to 4 of 4
  1. #1
    ebrommhead is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Nov 2012
    Location
    Mississauga
    Posts
    28

    Sending an Individual Email to Each Rep for his Sales Budget in Excel

    Tying to automatically email sales reps Excel files with sales history from a folder where each file has the reps' names. Downloaded the code below from John Ross and modified it to fit my needs, but cannot, for the life of me, create an email with the proper reps' addresses. It will create an email for each file in the folder with the proper file attached, but does not attach an address for that sales rep.

    Created Stremail with a dlookup and it populates the email address with the same rep name for all, ie the first in the list. Sometimes, it creates multiple emails with the same file until I abort it.

    Thought it would be relatively simple but no cigar.

    FYI the table for the sales reps emails is :SAname, saemail and matchingname.

    Example:
    saname saemail matchingname
    BILL FRENCH bfrench@colemancontainers.com BILL FRENCH.xlsx

    Added the matching name to match with the Excel file that is stored in the folder.

    Any assistance would be appreciated.

    Current Code is:




    Private Sub Command45_Click()

    Dim Stremail As String
    Dim StrFile As String, StrPath As String
    'Dim appOutLook As Outlook.Application
    'Dim MailOutLook As Outlook.MailItem

    Set appOutLook = CreateObject("Outlook.Application")

    'define where we look for our folder full of pdf files

    StrPath = CurrentProject.Path & "\excelfilestorepsforinputdata"

    StrFile = Dir(CurrentProject.Path & "\excelfilestorepsforinputdata\*.xlsx")


    'loop that directory

    Do While Len(StrFile) > 0


    'create a new email window
    Set MailOutLook = appOutLook.CreateItem(olMailItem)
    With MailOutLook 'apply to each new email window
    'Stremail = DLookup("[saemail]", "[salesmens emails]", "[matchingname]='" & Dir(CurrentProject.Path & "\excelfilestorepsforinputdata\*.xlsx") & "'")

    '.BodyFormat = olFormatRichText
    .SentOnBehalfOfName = "user@example.com" 'send as
    '.To = Stremail

    .Subject = "Sales History for Current Sales Plan"
    .HTMLBody = "Please open the attached file to view your data.<br><br>" ' & _
    "<h3>Accounts Receivable</h3><b>Billing Department</b><br>Office: 518-555-6393<br>example.com "
    .Attachments.Add StrPath + StrFile
    .Display
    '.Send
    .Save 'save as draft
    End With
    StrFile = Dir
    Loop

    'message box when the loop finishes
    MsgBox "Done!", vbOKOnly

    End Sub

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    Looks like need to un-comment the 'Stremail= and '.To lines - remove apostrophe at beginning.

    But that DLookup() could be simplified with variable, since you have one, use it:

    Stremail = DLookup("[saemail]", "[salesmens emails]", "[matchingname]='" & StrFile & "'")
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    ebrommhead is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Nov 2012
    Location
    Mississauga
    Posts
    28
    Thanks very much June7. Worked well. When the emails are completed, it seems to stay in Outlook. Is there any code to get back to MS Access?

  4. #4
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    Note for future: Please place lengthy code within CODE tags to retain indentation and readability.

    You mean the email message stays open? You haven't sent it, only saved as draft. If you want to only save and not send use:

    .Close (olSave)

    If you want to send, then uncomment the Send line and comment or remove Save.

    Use (before .Send line)
    .DeleteAfterSubmit = True if you want to send without saving in Sent folder.



    Most would loop through a recordset of email addresses and go find the file to attach. You are doing the opposite. Rather unusual but does work.


    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 24
    Last Post: 08-27-2019, 04:44 PM
  2. Replies: 1
    Last Post: 11-07-2016, 11:18 AM
  3. Replies: 7
    Last Post: 01-11-2015, 09:45 PM
  4. Replies: 3
    Last Post: 08-16-2013, 04:15 PM
  5. Sending excel file in an email
    By Jim.H. in forum Access
    Replies: 1
    Last Post: 01-29-2012, 12:56 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums