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.
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.
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
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
A forum community dedicated to tech experts and enthusiasts. Come join the discussion about articles, computer security, Mac, Microsoft, Linux, hardware, networking, gaming, reviews, accessories, and more!