+ Reply to Thread
Results 1 to 12 of 12

Automatically generate email for outlook based on date

  1. #1
    Registered User
    Join Date
    08-28-2020
    Location
    Germany
    MS-Off Ver
    Office 365
    Posts
    62

    Automatically generate email for outlook based on date

    Good day,

    I need to generate an email in combination with Outlook. The email should be generated based on a date (if it is older than today's date, then generate the email). Once generated, it should somehow mark the person so that when another person is opening the file too, it is not generating the same email again (spam).
    The email text should include some information from the Excel file, so replace some words with information from the specific cell.

    I added a detailed explenation into the sheet "(Emails)". I would be very thankful if anyone can help me or at least give me some good hints. I put much effort into learning VBA, but since I'm very new (learning for a week now), this is nothing I can do on my own at the moment, but I'm in need of it.

    Thanky ou in advance, I appreciate every tip, trick and support!

    Greetings
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    05-29-2020
    Location
    NH USA
    MS-Off Ver
    365
    Posts
    2,103

    Re: Automatically generate email for outlook based on date

    Will you always send one email per row? Or do you want to aggregate emails for the same email address, such that you send info for two rows at once?

  3. #3
    Forum Moderator davesexcel's Avatar
    Join Date
    02-19-2006
    Location
    Regina
    MS-Off Ver
    MS 365
    Posts
    13,474

    Re: Automatically generate email for outlook based on date

    Here is a generic email code with a sample workbook

    https://sites.google.com/view/xlorat...p_5CEUbJlPH79R

  4. #4
    Registered User
    Join Date
    08-28-2020
    Location
    Germany
    MS-Off Ver
    Office 365
    Posts
    62

    Re: Automatically generate email for outlook based on date

    Thanks for the hint! No, it is totally fine to generate one email per row. Normally the list is not too long (it's just for the working students and interns), most likely they will only receive one email.

    That they receive multiple emails a day would only happen if I set the same date for a job to re-search and if it is the same manager. This most likely doesn't happen. :-)

  5. #5
    Registered User
    Join Date
    08-28-2020
    Location
    Germany
    MS-Off Ver
    Office 365
    Posts
    62

    Re: Automatically generate email for outlook based on date

    Thank you, I'll check it out!

  6. #6
    Forum Expert
    Join Date
    05-29-2020
    Location
    NH USA
    MS-Off Ver
    365
    Posts
    2,103

    Re: Automatically generate email for outlook based on date

    Here is the first iteration. I'll work on your "sick" request.
    Attached Files Attached Files
    Last edited by maniacb; 09-10-2020 at 09:43 AM. Reason: Corrections to response

  7. #7
    Registered User
    Join Date
    08-28-2020
    Location
    Germany
    MS-Off Ver
    Office 365
    Posts
    62

    Re: Automatically generate email for outlook based on date

    I'll check it as soon as I'm home since I'm not allowed to download it at my work computer. Thank you so much in advance!

  8. #8
    Forum Expert
    Join Date
    05-29-2020
    Location
    NH USA
    MS-Off Ver
    365
    Posts
    2,103

    Re: Automatically generate email for outlook based on date

    I made the update to ask up front if you want to proceed with sending the email, and how many are ready to be sent. In the email code, one line ".display," ensures the email will display before being sent. If you don't want to preview the email, then comment out the " '.display" line and uncomment the ".send" line. Or you can activate both if you would like.



    Please Login or Register  to view this content.
    On the workbook module, I used this code:



    Please Login or Register  to view this content.
    I also added a Sent column to track what has been Emailed.
    Attached Files Attached Files

  9. #9
    Registered User
    Join Date
    08-28-2020
    Location
    Germany
    MS-Off Ver
    Office 365
    Posts
    62

    Re: Automatically generate email for outlook based on date

    It is so amazing, thank you so much! If we ever meet, I owe you some beers ;-)

    Just small things:
    1) Is it 1. of all possible that in the email text after "Mit freundlichen Grüßen" it automatically adds the name of the user who is opening the Excel file? Since we're all using our business accounts, it is the correct full name.
    2) If there is no reminder to be send, do not open the popup window when opening the Excel file
    3) If there is only one reminder to be send, there should be a slightly different text, if more than 1 another text (because of singular-plural).

    Thank you so, so much!

  10. #10
    Forum Expert
    Join Date
    05-29-2020
    Location
    NH USA
    MS-Off Ver
    365
    Posts
    2,103

    Re: Automatically generate email for outlook based on date

    Here you go. 1) to collect the User name, All we need is to use the Application.UserName property. For 2 and 3, I added parenthesis around the s, so that the msg shows "email(s)" to denote plural or singular. Let me know if you have any further questions.
    Attached Files Attached Files
    Last edited by maniacb; 09-10-2020 at 04:35 PM. Reason: Corrections to response

  11. #11
    Registered User
    Join Date
    08-28-2020
    Location
    Germany
    MS-Off Ver
    Office 365
    Posts
    62

    Re: Automatically generate email for outlook based on date

    I already fixed it on my own and I'm proud, haha

    Here`s how I did it:
    HTML Code: 

  12. #12
    Registered User
    Join Date
    08-28-2020
    Location
    Germany
    MS-Off Ver
    Office 365
    Posts
    62

    Re: Automatically generate email for outlook based on date

    Answered my own question
    Last edited by Mievo; 09-11-2020 at 02:56 AM. Reason: Answered my own question

+ 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. [SOLVED] Macro to generate email, modify to update subject and body based on date
    By Howardc1001 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 06-09-2020, 11:18 AM
  2. Macros to email a row with header based on due date and specific email using outlook
    By mscarrie in forum Excel Programming / VBA / Macros
    Replies: 19
    Last Post: 07-30-2019, 04:48 AM
  3. Send automatic Outlook email based on Due Date
    By WesternPartner64 in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 12-02-2018, 10:11 PM
  4. Send email automatically through outlook on near due date with attachment
    By janakkaneria in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 04-30-2018, 11:22 AM
  5. Replies: 0
    Last Post: 01-30-2016, 11:49 AM
  6. Excel to generate email to one (1) specific address based on specified date.
    By excelfanatek in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 01-03-2014, 04:07 PM
  7. Replies: 5
    Last Post: 02-22-2013, 02:00 AM

Tags for this Thread

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