+ 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
    Valued Forum Contributor
    Join Date
    05-29-2020
    Location
    NH USA
    MS-Off Ver
    365
    Posts
    1,045

    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
    12,686

    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
    Valued Forum Contributor
    Join Date
    05-29-2020
    Location
    NH USA
    MS-Off Ver
    365
    Posts
    1,045

    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
    Valued Forum Contributor
    Join Date
    05-29-2020
    Location
    NH USA
    MS-Off Ver
    365
    Posts
    1,045

    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
    Valued Forum Contributor
    Join Date
    05-29-2020
    Location
    NH USA
    MS-Off Ver
    365
    Posts
    1,045

    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: 
    Option Explicit
    
    Sub SendEmailCheck()
    'This code checks for rows with past days and looks at column N for blank value to count how many emails to send
        Dim i, Cnt, Rslt As Integer
        Application.ScreenUpdating = False
        Cnt = 0
        For i = 6 To Worksheets("Mitarbeiter").Cells(Rows.Count, "M").End(xlUp).Row
            If ((Worksheets("Mitarbeiter").Cells(i, "L").Value) = "") And (Worksheets("Mitarbeiter").Cells(i, "N").Value = "" And ((Worksheets("Mitarbeiter").Cells(i, "D").Value) < Now)) Then
                Cnt = Cnt + 1
            End If
        Next i
        
        If Cnt < 1 Then Exit Sub
        
        If Cnt = 1 Then
            Rslt = MsgBox("Es ist eine Erinnerung bezüglich ablaufender Stellen zu verschicken." & vbNewLine & vbNewLine & "Email jetzt generieren?", vbYesNo + vbQuestion, "Erinnerungsmails")
            If Rslt = "6" Then
                MsgBox "Die Email wird nun generiert und in Outlook geöffnet."
            
                SendEmailsifPastDate
                
            Else
                MsgBox "Beim nächsten Öffnen erfolgt eine erneute Abfrage.", vbInformation, "Erinnerungsemails"
            End If
        End If
        If Cnt > 1 Then
            Rslt = MsgBox("Es sind " & Cnt & " Erinnerungen bezüglich ablaufender Stellen zu verschicken." & vbNewLine & vbNewLine & "Emails jetzt generieren?", vbYesNo + vbQuestion, "Erinnerungsmails")
            If Rslt = "6" Then
                MsgBox "Die Emails werden nun generiert und in Outlook geöffnet."
            
                SendEmailsifPastDate
                
            Else
                MsgBox "Beim nächsten Öffnen erfolgt eine erneute Abfrage.", vbInformation, "Erinnerungsemails"
            End If
        End If
        
        With Application
            .CutCopyMode = False
            .ScreenUpdating = True
        End With
    End Sub
    
    Sub SendEmailsifPastDate()
    'This solution checks for rows with past days and looks at column N for blank value to send emails
        Dim i As Integer
        'Dim lr As Integer
        Application.ScreenUpdating = False
        'lr = Worksheets("Mitarbeiter").Cells(Rows.Count, "B").End(xlUp).Row 'For troubleshooting
        
        For i = 6 To Worksheets("Mitarbeiter").Cells(Rows.Count, "M").End(xlUp).Row
            If ((Worksheets("Mitarbeiter").Cells(i, "L").Value) <= Now) And (Worksheets("Mitarbeiter").Cells(i, "N").Value = "") Then
                Send_newemail2 (i)
                Worksheets("Mitarbeiter").Cells(i, "N").Value = Date
            End If
        Next i
        With Application
            .CutCopyMode = False
            .ScreenUpdating = True
        End With
    End Sub
    
    Sub Send_newemail2(i)
        Dim OutApp As Object
        Dim OutMail As Object
        Dim rng As Range
        Dim rngt As Range
    
        Set OutApp = CreateObject("Outlook.Application")
        Set OutMail = OutApp.CreateItem(0)
        'Set rngt = Worksheets("Mitarbeiter").Range("A1:M1")
        'Set rng = Worksheets("Mitarbeiter").Range("A" & i & ":M" & i)
        
        With OutMail
                '.SentOnBehalfOfName = "abc@xyz.com" for second email - needs to be active on exchange server
                .To = Worksheets("Mitarbeiter").Cells(i, "M").Value
                .CC = ""
                .Subject = "Auslaufender Vertrag, " & Worksheets("Mitarbeiter").Cells(i, "B")
                '.HTMLbody = RangetoHTML(rngt) & vbNewLine & RangetoHTML(rng)
                .Body = "Sehr geehrte/r Herr/Frau " & Worksheets("Mitarbeiter").Cells(i, "G") & "," & vbNewLine & vbNewLine & _
    "auch wenn es sich hierbei um eine automatisierte Email handelt, folgender wichtiger Hinweis:Ihr/e Student/in " & Worksheets("Mitarbeiter").Cells(i, "B") & " mit der Stelle " & Worksheets("Mitarbeiter").Cells(i, "c") & " verlässt das Unternehmen laut Vertragsdatum zum " & Worksheets("Mitarbeiter").Cells(i, "E") & "." & vbNewLine & vbNewLine & _
    "Sollten Sie die Stelle nachbesetzen wollen, möchten wir Sie bitten uns zeitnah eine Information zukommen zu lassen, sodass wir einen reibungslosen Ablauf gewährleisten können. " & vbNewLine & _
    "Für den Fall dass wir nichts von Ihnen hören, gehen wir davon aus, dass eine Nachbesetzung für die Stelle " & Worksheets("Mitarbeiter").Cells(i, "c") & " aktuell nicht gewünscht ist." & vbNewLine & _
    "Bei Fragen stehen wir Ihnen gerne jederzeit zur Verfügung." & vbNewLine & vbNewLine & _
    "Mit freundlichen Grüßen" & vbNewLine & vbNewLine & _
    Application.UserName
                .Display    'DELETE THIS LINE IF USING SEND
                '.send
       End With
                
       Set OutMail = Nothing
       Set OutApp = Nothing
        
    End Sub

  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