Tech Support Guy banner
Status
Not open for further replies.

Reminder E-Mail with Excel

Solved 
1K views 10 replies 2 participants last post by  Keebellah 
#1 · (Edited)
Hi, Its my first post here. I need to send reminder mails from outlook based on excel
I searching for the VBA which according to my needs and I find in this Forum, but I have difficulty understanding. VBA that I adjust not functioning.

I attach the following

I need your expert solution

Any help appreciated and thank you.
 

Attachments

#2 ·
Hi welcome to the forum, since it's your first post I assume you took the time to search for similar questions?
Well, I suggest you do just that there are many answers which with minor editing will do the task for you, we can always jump-in when you get stuck but just asking to avoid doing some 'homework' before asking, come on, we're all here to help but at least put your own effort into it too.
 
#6 ·
Minor modifications but I still do not know what you really want
Code:
Public Sub eMail()
Dim OutApp      As Object
Dim OutMail     As Object
Dim lRow        As Integer
Dim i           As Integer
Dim toDate      As Date
Dim toList      As String
Dim eSubject    As String
Dim eBody       As String

With Application
    .ScreenUpdating = False
    .EnableEvents = False
    .DisplayAlerts = False
End With

Sheets(1).Select
lRow = Cells(Rows.Count, 42).End(xlUp).Row

For i = 2 To lRow
    If Left(Cells(i, 63), 42) <> "Mail" Then
        toDate = Cells(i, 22).Value 'gets date fr column V
        If toDate - Date <= 30 Then        '63=BK  42-AP
            Set OutApp = CreateObject("Outlook.Application")
            Set OutMail = OutApp.CreateItem(0)

            toList = Cells(i, 42)    'gets the recipient from col AP
            eSubject = "Reminder Form Evaluasi" & " is due on " & Cells(i, 22)  '22=V
            eBody = "Dear " & Cells(i, 43) & vbCrLf & vbCrLf & "Bersama ini kami hendak mengingatkan kembali perihal Evaluasi Karyawan an " & Cells(i, 8) & " yang KONTRAK kerjanya berakhir pada tanggal " & Cells(i, 22) & " Kami mohon agar dapat menyerahkan kembali FORM EVALUASI KARYAWAN ke HRD untuk ditandatangi oleh HR & GA Dept Head dan Direktur " & vbCrLf & vbCrLf & "Atas perhatian dan kerjasamanya kami ucapkan terimakasih" & vbCrLf & vbCrLf & "NOTE:" & vbCrLf & "Khusus Divisi Marketing, mohon Form Evaluasi Karyawan telah  ditandatangani oleh KADIV terlebihdahulu" & vbCrLf & vbCrLf & "Best Regards," & vbCrLf & "HR Departement"
       
            On Error Resume Next
            With OutMail
                .To = toList
                .CC = ""
                .BCC = ""
                .Subject = eSubject
                .Body = eBody
                .bodyformat = 1
                .Display    ' ********* Creates draft emails. Comment this out when you are ready
                .Save       ' ********* Saves mail to draft. Comment this out when you are ready
                '.Send      '********** UN-comment this when you  are ready to go live
            End With

            On Error GoTo 0
            Set OutMail = Nothing
            Set OutApp = Nothing
            Cells(i, 63) = "Mail Sent " & Date + Time 'Marks the row as "email sent in Column A"
        End If
    End If
Next i

ActiveWorkbook.Save

With Application
    .ScreenUpdating = True
    .EnableEvents = True
    .DisplayAlerts = True
End With
End Sub
I added the Save so it will not send so you can test.
I would replace all the column number with letters, makes it easier to understand for example

Code:
 Cells(i, 42)   -->   Cells(i, "AP")
 
#7 ·
I hope you're still busy with this
Anyway, the If statement needs to be corrected
Code:
For i = 2 To lRow
    If Left(Cells(i, 63), 4) <> "Mail" And Cells(i, 22).Value <> "" And Cells(i, 42).Value <> "" Then        '63=BK  22=V   42=AP
        toDate = Cells(i, 22).Value 'gets date fr column V
But you should still explain what the column Aktif means (for you) I understand the idea but you MUST explain what you want if you want anyone to help you
 
Status
Not open for further replies.
You have insufficient privileges to reply here.
Top