crook_101
Well-known Member
- Joined
- Oct 20, 2008
- Messages
- 687
Hi all,
I have inherited an excel sheet for our local group that uses VB to send emails - I am an old rusty coder and have cobbled together some code that runs, generates & sends an email. What it doesn't do and I would like it to do is to loop through column BR (If Sheet3.Cells(i, 70).Value = Date Then) looking for all populated date fields and then send the relevant email. Any nudges in the right direction / code would be much appreciated.
Many thanks from a rusty guy just getting back into Excel & VB after retirement!!
oSmtp.Asynchronous = 1
oSmtp.SendMail
Set oSmtp = Nothing
End Sub
I have inherited an excel sheet for our local group that uses VB to send emails - I am an old rusty coder and have cobbled together some code that runs, generates & sends an email. What it doesn't do and I would like it to do is to loop through column BR (If Sheet3.Cells(i, 70).Value = Date Then) looking for all populated date fields and then send the relevant email. Any nudges in the right direction / code would be much appreciated.
Many thanks from a rusty guy just getting back into Excel & VB after retirement!!
VBA Code:
Public Sub IJSendMailTo()
Dim sender As String
Dim name As String
Dim address As String
Dim subject As String
Dim body As String
Dim bodyFormat As Integer
Dim i As Integer
For i = 2 To Sheet3.Cells(Rows.Count, 1).End(xlUp).Row
If Sheet3.Cells(i, 70).Value = Date Then
sender = "noreply@northumbriabloodbikes.org.uk"
name = "AlanK"
address = Sheet3.Cells(i, 7).Value ' the TO address
subject = "NBB - Invitation To Arrange Your Annual Driver Assessment"
body = "<Body style = font-size:12pt; font-family:Arial>" & "Hi " & Sheet3.Cells(i, 3) & "," & "<br>NBB ID:- " & Sheet3.Cells(i, 1) _
& "<br><br>Your Annual Driver Assessment is/was due on <strong>" & Sheet3.Cells(i, 62) & ".</strong><br>" _
& "<strong><p style=color:red;>" & "Do not book a Car Shift after this date unless your Assessment is completed as you will not be insured.</strong><br><br>" _
& "<p style=color:black;>" & "Please would you arrange your assessment with one of the Assessors below.<br><br>" _
& " ( Durham City )<br><br>" _
& " ( Alnwick )<br><br>" _
& " ( Ryton )<br><br>" _
& " ( Sunderland )</p>" _
& "Thank you for your continued commitment and support."
End If
Next
bodyFormat = 1
Set oSmtp = New EASendMailObjLib.Mail
oSmtp.LicenseCode = "TryIt" ' Here goes your license code for the software; for now, we are using the trial version
' Please change the server address, username, and password to the ones you will be using
' ALK The KUALO (NBB's) email creds
oSmtp.ServerAddr = "XXXXXX.net"
oSmtp.UserName = "noreply@northumbriabloodbikes.org.uk"
oSmtp.Password = "XXXXX"
oSmtp.ServerPort = 465
oSmtp.ConnectType = 3
oSmtp.FromAddr = sender
oSmtp.AddRecipient name, address, 0
oSmtp.subject = subject
oSmtp.bodyFormat = bodyFormat
oSmtp.BodyText = bodyoSmtp.Asynchronous = 1
oSmtp.SendMail
Set oSmtp = Nothing
End Sub
