Sending Emails through Excel

[cja65]

HI, I am trying to educate myself on sending emails from my excel spreadsheet based on a due date. What I have read so far is I need to have the EASendMailOBJ ActiveX Object library in available references. It is not an available library, and I can't see how to download it to add it. Is this the correct way to automate emails? Thanks.

 

[6StringJazzer]

I am not familiar with EASendMail but from what I've read it seems to be a COM library for sending mail directly through you SMTP server. It's more common to integrate Excel with Outlook to do things like this, since any sent mail will be in your Outlook Sent folder, and so on. But if you don't Outlook to begin with, no advantage there.

Tell us about how you use email.

 

[Logit]

One example :

Option Explicit

Sub eMail()
Dim lRow As Integer
Dim i As Integer
Dim toDate As Date
Dim toList As String
Dim eSubject As String
Dim eBody As String
Dim OutApp
Dim OutMail

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

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

For i = 2 To lRow
toDate = Sheets("Sheet1").Cells(i, 1)

If toDate < Date Then: Exit Sub

  If Left(Cells(i, 5), 4) <> "Mail" And toDate = Date Then
 
             Set OutApp = CreateObject("Outlook.Application")
             Set OutMail = OutApp.CreateItem(0)
        
                toList = Cells(i, 2)    'gets the recipient from col B
                eSubject = Cells(i, 3)  'gets subject from col C
                eBody = Cells(i, 4)     'gets body of email from col D
                
                'enter email body here
                
                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
                '.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, 5) = "Mail Sent " & Date + Time 'Marks the row as "email sent in Column C"
            
    End If
Next i
ActiveWorkbook.Save

With Application
    .ScreenUpdating = True
    .EnableEvents = True
    .DisplayAlerts = True
End With
MsgBox "All emails have been sent. ", vbInformation, "Email Notice "

End Sub

Sub sbInsertingRows()
    'Inserting a Row at at Row 2
    Range("A2").EntireRow.Insert
End Sub

Download example file : Internxt Drive – Private & Secure Cloud Storage

Second example :

Option Explicit
Sub eMail()
Dim lRow As Integer
Dim i As Integer
Dim toDate As Date
Dim toList As String
Dim eSubject As String
Dim eBody As String
Dim OutApp
Dim OutMail

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

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

For i = 2 To lRow
toDate = Replace(Cells(i, 13), ".", "/")
  If Left(Cells(i, 5), 4) <> "Mail" And toDate = Date Then '<= 1 is set to send email 1 days prior to the send date / SHEET 1
                                                                'Change the number as desired.
     Set OutApp = CreateObject("Outlook.Application")
     Set OutMail = OutApp.CreateItem(0)

        toList = Cells(i, 4)    'gets the recipient from col D
        eSubject = "Project " & Cells(i, 2) & " is due on " & Cells(i, 3)
        eBody = "Dear " & Cells(i, 1) & vbCrLf & vbCrLf & "Please update your project status."
        
        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
        '.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, 5) = "Mail Sent " & Date + Time 'Marks the row as "email sent in Column A"
End If
Next i

ActiveWorkbook.Save

With Application
    .ScreenUpdating = True
    .EnableEvents = True
    .DisplayAlerts = True
End With
End Sub

Download sample file : Internxt Drive – Private & Secure Cloud Storage

 

[cja65]

I am not familiar with EASendMail but from what I've read it seems to be a COM library for sending mail directly through you SMTP server. It's more common to integrate Excel with Outlook to do things like this, since any sent mail will be in your Outlook Sent folder, and so on. But if you don't Outlook to begin with, no advantage there.

Tell us about how you use email.

Thanks Jeff. I am creating a spreadsheet that tracks our companies projects. I record dates for different phases of the project as they are completed. I am wanting to send the Project Manager an email if the last updated stage is greater than 60 days compared to TODAY(). It would be great if the email came from my outlook account so I can verify in my Sent Folder, but a generic email is fine as well.

 

[cja65]

One example :

Option Explicit

Sub eMail()
Dim lRow As Integer
Dim i As Integer
Dim toDate As Date
Dim toList As String
Dim eSubject As String
Dim eBody As String
Dim OutApp
Dim OutMail

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

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

For i = 2 To lRow
toDate = Sheets("Sheet1").Cells(i, 1)

If toDate < Date Then: Exit Sub

  If Left(Cells(i, 5), 4) <> "Mail" And toDate = Date Then
 
             Set OutApp = CreateObject("Outlook.Application")
             Set OutMail = OutApp.CreateItem(0)
       
                toList = Cells(i, 2)    'gets the recipient from col B
                eSubject = Cells(i, 3)  'gets subject from col C
                eBody = Cells(i, 4)     'gets body of email from col D
               
                'enter email body here
               
                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
                '.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, 5) = "Mail Sent " & Date + Time 'Marks the row as "email sent in Column C"
           
    End If
Next i
ActiveWorkbook.Save

With Application
    .ScreenUpdating = True
    .EnableEvents = True
    .DisplayAlerts = True
End With
MsgBox "All emails have been sent. ", vbInformation, "Email Notice "

End Sub

Sub sbInsertingRows()
    'Inserting a Row at at Row 2
    Range("A2").EntireRow.Insert
End Sub

Download example file : Internxt Drive – Private & Secure Cloud Storage

Second example :

Option Explicit
Sub eMail()
Dim lRow As Integer
Dim i As Integer
Dim toDate As Date
Dim toList As String
Dim eSubject As String
Dim eBody As String
Dim OutApp
Dim OutMail

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

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

For i = 2 To lRow
toDate = Replace(Cells(i, 13), ".", "/")
  If Left(Cells(i, 5), 4) <> "Mail" And toDate = Date Then '<= 1 is set to send email 1 days prior to the send date / SHEET 1
                                                                'Change the number as desired.
     Set OutApp = CreateObject("Outlook.Application")
     Set OutMail = OutApp.CreateItem(0)

        toList = Cells(i, 4)    'gets the recipient from col D
        eSubject = "Project " & Cells(i, 2) & " is due on " & Cells(i, 3)
        eBody = "Dear " & Cells(i, 1) & vbCrLf & vbCrLf & "Please update your project status."
       
        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
        '.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, 5) = "Mail Sent " & Date + Time 'Marks the row as "email sent in Column A"
End If
Next i

ActiveWorkbook.Save

With Application
    .ScreenUpdating = True
    .EnableEvents = True
    .DisplayAlerts = True
End With
End Sub

Download sample file : Internxt Drive – Private & Secure Cloud Storage

Thanks Logit. I am working on it now and will see if I can get it to work.

 

[6StringJazzer]

I see that Logit provided an Outlook solution just after my question.

(BTW you don't have to quote an entire post for a brief reply, takes up a lot of space.)