+ Reply to Thread
Results 1 to 22 of 22

Send outlook mail based on a today's date and customer name

  1. #1
    Registered User
    Join Date
    04-05-2017
    Location
    Pittsburg Ca
    MS-Off Ver
    Excel 2007
    Posts
    13

    Send outlook mail based on a today's date and customer name

    Hello all!

    I have some working code but I need one more thing added to it and I am stumped.

    Goal:
    I have a Customer Shipping Tracking Log (excel 07) and I need it to automatically email (outlook) tracking information to customers at the end of the day. I need it to only send emails for orders that have ship dates that are today's date.

    Plan:
    Before application close, message box "do you want to send out tracking information from today's date?" if yes, run the code and send out emails to the customers who have emails stored in the Mailinfo tab.

    What I have:
    The code works perfectly, but it takes all the data for each customer from the active worksheet. I need it to only email rows with today's date.

    Can anyone help? Thank you.
    Attached Files Attached Files

  2. #2
    Forum Expert jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

    Re: Send outlook mail based on a today's date and customer name

    Hi manieldunk

    Welcome to the Forum!!!

    What Date Column you working with Date (Column B) or Ship Date (Column G)?
    John

    If you have issues with Code I've provided, I appreciate your feedback.

    In the event Code provided resolves your issue, please mark your Thread as SOLVED.

    If you're satisfied by any members response to your issue please use the star icon at the lower left of their post.

  3. #3
    Registered User
    Join Date
    04-05-2017
    Location
    Pittsburg Ca
    MS-Off Ver
    Excel 2007
    Posts
    13

    Re: Send outlook mail based on a today's date and customer name

    Ship Date. Column G.

    Thank you

  4. #4
    Forum Expert jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

    Re: Send outlook mail based on a today's date and customer name

    Hi manieldunk

    Try This...replace the Workbook_BeforeClose Code with this...
    Please Login or Register  to view this content.
    Attached Files Attached Files

  5. #5
    Forum Expert jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

    Re: Send outlook mail based on a today's date and customer name

    Hi manieldunk

    Probably should have said...on a Copy of your File replace the Workbook_BeforeClose Code with this...
    Please Login or Register  to view this content.

  6. #6
    Registered User
    Join Date
    04-05-2017
    Location
    Pittsburg Ca
    MS-Off Ver
    Excel 2007
    Posts
    13

    Re: Send outlook mail based on a today's date and customer name

    AMAZING! Thank you very much! I am not sure if this is possible, but is there any way to use an outlook email template for the emails would like to have them look as professional as possible? How about adding attachments?

    I can't explain how much I appreciate your help!

  7. #7
    Registered User
    Join Date
    04-05-2017
    Location
    Pittsburg Ca
    MS-Off Ver
    Excel 2007
    Posts
    13

    Re: Send outlook mail based on a today's date and customer name

    Actually one issue. It is filtering the ship dates on only the month number and not the month and day. My emails are including 4/5 and 4/9 4/3 etc... I need it to be only the current date.

  8. #8
    Forum Expert jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

    Re: Send outlook mail based on a today's date and customer name

    Hi manieldunk

    Yes...you'll need to define it...what's the Template look like...
    is there any way to use an outlook email template for the emails
    and Yes...you'll also need to define this...what attachment(s)...
    How about adding attachments?

  9. #9
    Forum Expert jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

    Re: Send outlook mail based on a today's date and customer name

    Hi manieldunk

    You'll need to show me the offending File...I had to mock up Data in your original Sample File to test the Code so there may be an issue that I've not anticipated.
    Actually one issue. It is filtering the ship dates on only the month number and not the month and day. My emails are including 4/5 and 4/9 4/3 etc... I need it to be only the current date.

  10. #10
    Registered User
    Join Date
    04-05-2017
    Location
    Pittsburg Ca
    MS-Off Ver
    Excel 2007
    Posts
    13

    Re: Send outlook mail based on a today's date and customer name

    Jaslake, I am going to get everything together and more organized. I will be back in a day or two. Thank you for your help so far.

  11. #11
    Forum Expert jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

    Re: Send outlook mail based on a today's date and customer name

    Your welcome...

  12. #12
    Registered User
    Join Date
    04-05-2017
    Location
    Pittsburg Ca
    MS-Off Ver
    Excel 2007
    Posts
    13

    Re: Send outlook mail based on a today's date and customer name

    So far everything seems to be almost working perfectly

    Recap:
    Goal = I have a Customer Shipping Tracking Log (excel 07) and I need it to automatically email (outlook) tracking information to customers at the end of the day. I need it to only send emails for orders that have ship dates that are today's date. The email information is extracted from the Mailinfo tab which is updated often. Some of our customers have multiple orders per day.

    Current issues with code:
    The email message is including the excel information from the ship dates with this month not specifically ship dates with today's date.

    Additional requests:
    I would like for the email to be sent through an email template I have created.
    I would like that template to include an attachment
    If it is possible it would be helpful if the email template did not include my automatic signature

    See attached. For excel and attachment (For some reason it is not letting me upload my outlook template)

    Thank you
    Attached Files Attached Files

  13. #13
    Forum Expert jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

    Re: Send outlook mail based on a today's date and customer name

    Hi manieldunk

    To upload the Template change the File Extension. For example...TemplateName.oft.txt. Indicate that you've done so such that any helper knows that the File needs renamed.

    Is the attachment the PDF File?
    I would like that template to include an attachment

  14. #14
    Registered User
    Join Date
    04-05-2017
    Location
    Pittsburg Ca
    MS-Off Ver
    Excel 2007
    Posts
    13

    Re: Send outlook mail based on a today's date and customer name

    The PDF file is the attachment. I cant get the .OFT file to upload because it is too large. I have changed the file extension and zipped the folder but still to big. I uploaded just a plain .txt of the template in case that will help.
    Attached Files Attached Files

  15. #15
    Forum Expert jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

    Re: Send outlook mail based on a today's date and customer name

    Hi manieldunk

    This Code in the attached appears to resolve this issue...
    The email message is including the excel information from the ship dates with this month not specifically ship dates with today's date.
    Regarding this...I can't imagine the .OFT File is too large...makes no sense...I'd like you to try again. I personally find creating Outlook .OFT Templates to be a painful exercise. If you're not successful then I'll give it a go.
    I cant get the .OFT file to upload because it is too large. I have changed the file extension and zipped the folder but still to big.
    Code:
    Please Login or Register  to view this content.
    Attached Files Attached Files

  16. #16
    Registered User
    Join Date
    04-05-2017
    Location
    Pittsburg Ca
    MS-Off Ver
    Excel 2007
    Posts
    13

    Re: Send outlook mail based on a today's date and customer name

    Attached you will find the email template. (file extension needs to be changed from .OFT.txt to .OFT)

    I just noticed that the reason the file was too large was because there was an attachment in the template.

    Thank you for your patience.
    Attached Files Attached Files

  17. #17
    Forum Expert jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

    Re: Send outlook mail based on a today's date and customer name

    Hi manieldunk

    I'll play with it...probably not tonight. If Ohio weather cooperates have Spring outside stuff to do...it'll be off and on...I do keep in regular contact... get back to you soon.

  18. #18
    Forum Expert jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

    Re: Send outlook mail based on a today's date and customer name

    Hi manieldunk

    You'll need to play with this...Email OFT Templates are a bit of a mystery to me.

    For testing purposes place ALL the attached Files in a NEW FOLDER. In that New Folder, rename Untitled1.oft.txt to Untitled1.oft

    Open the Excel File and, in VBA, set your References as shown...save the File...then close the File...the Code will/should fire...let me know if you get as expected.

    2017-04-07_18-26-42.jpg

  19. #19
    Registered User
    Join Date
    04-05-2017
    Location
    Pittsburg Ca
    MS-Off Ver
    Excel 2007
    Posts
    13

    Re: Send outlook mail based on a today's date and customer name

    Works! Thank you soo much. I appreciate the knowledge.

  20. #20
    Forum Expert jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

    Re: Send outlook mail based on a today's date and customer name

    Hi manieldunk

    You're welcome...glad I could help. Thanks for the Rep.

    I'm pleasantly surprised that it worked first time out of the box as YOU wanted it to work.

  21. #21
    Registered User
    Join Date
    04-05-2017
    Location
    Pittsburg Ca
    MS-Off Ver
    Excel 2007
    Posts
    13

    Re: Send outlook mail based on a today's date and customer name

    Hello Jaslake,

    These automated emails have been working great, but our customers are asking for an improvement.

    Currently the workbook copies a range of cells, takes a picture of that range, and inserts the picture into an outlook template email. Since the range is inserted as a picture, our customers are unable to copy and paste the tracking numbers from the picture.

    Is there a way to copy the range as a table and insert the table into the outlook email template? Or any other options so the text can be copied from the email?

    Thank you,

    Dan

  22. #22
    Forum Expert jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

    Re: Send outlook mail based on a today's date and customer name

    Hi Dan

    As this Thread has been marked "SOLVED" it won't get much traffic. Please start a new Thread with your revised requirements; attach file(s) as necessary to describe your issue and desired outcome. Insert a Link to this Thread into your new Thread.
    Please PM me a Link to your new Thread if you wish and I'll look at it.
    Last edited by jaslake; 06-20-2018 at 02:16 PM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. VBA auto outlook 2016 to send e-mail based off criteria
    By broman5000 in forum Excel Programming / VBA / Macros
    Replies: 11
    Last Post: 01-30-2017, 04:15 PM
  2. [SOLVED] Code to send mail copying from Excel to a new outlook mail.
    By paz1987 in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 10-11-2015, 07:54 AM
  3. Based on Birthdate in excel , Automatically send outlook mail with birthday wishes
    By ajaypal.sp in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 02-06-2015, 08:28 PM
  4. Send e-mail with image on body (.html) on e-mail manager <> outlook
    By mariotnc in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 11-05-2012, 09:28 PM
  5. Filter based on today's date and send mail in outlook
    By kandanuru in forum Excel General
    Replies: 0
    Last Post: 06-15-2012, 04:59 AM
  6. outlook redemtion macro cant send mail if outlook is not open ?
    By okl in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 02-03-2010, 07:35 AM
  7. [SOLVED] How can I use Outlook express to send mail rather than Outlook by VBA code
    By new.microsoft.com in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 08-01-2005, 08:05 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1