Excel VBA Bulk Email with date and time email sent

Hi Claudia,

Try the following code. I tested the code the best that I could, however I had to take a few shortcuts because I do not use Outlook templates.

Option Explicit

Sub ESES()

  Dim wb As Workbook
  Dim ws As Worksheet

  Dim emailApplication As Object
  Dim emailItem As Object
  
  Dim iLastRow As Long
  Dim iRow As Long

  Dim sBody As String
  Dim sClientNumber As String
  Dim sTemplatePathAndFileName As String
  Dim sRecipient As String
  
  'Create Worksheet Object
  Set wb = ThisWorkbook          'The file that contains this code
  Set ws = wb.Sheets("Sheet1")   'Change this to your Sheet Name
  
  'Create the Outlook Object
  Set emailApplication = CreateObject("Outlook.Application")
  
  
  'Find the Last Blank Row in Column 'B'
  iLastRow = ws.Columns("B").Find(What:="*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row

  
  'Process emails until Column 'B' is BLANK
  For iRow = 2 To iLastRow
  
    'Get the Data For this Row  (remove Leading/Trailing Spaces)
    '.text gets item exactly as displayed ('####' if column is too narrow)
    '.value same as .value2 except when Currency or Date returns VBA Currency (truncated) or VBA Date
    '.value2 gets item as stored by Excel
    sClientNumber = Trim(ws.Cells(iRow, "A").Value)
    sRecipient = Trim(ws.Cells(iRow, "B").Value)
    sTemplatePathAndFileName = Trim(ws.Cells(iRow, "K").Value)
    
    'Get the Email Object
    Set emailItem = emailApplication.CreateItemFromTemplate(sTemplatePathAndFileName)

    emailItem.SendUsingAccount = "myemail@email.com"

    emailItem.to = sRecipient
    emailItem.Subject = "Business Appointment"

    sBody = emailItem.HTMLbody
    sBody = Replace(sBody, "#$ClientNumber#", sClientNumber)
    emailItem.HTMLbody = sBody

    emailItem.Display
    'emailItem.Send     'Use this to Send without preview
  
    'Output Date And Time (several Examples)
    ws.Cells(iRow, "X").Value = Format(Now(), "dddd mmmm d, yyyy h:mm AM/PM")
    ws.Cells(iRow, "Y").Value = Format(Now(), "mmm d, yyyy h:mm AM/PM")       'Column Y displays date in underlying Numberformaat for that column
    ws.Cells(iRow, "Z").Value = Now()
    ws.Cells(iRow, "Z").NumberFormat = "mmm d, yyyy hh:mm AM/PM"
  
  
    'Clear the Email Item Object
    Set emailItem = Nothing
    
  Next iRow
  
  'AutoFormat the Date Columns
  ws.Columns("X:Z").Columns.AutoFit
  
  
  'Clear Object Pointers
  Set wb = Nothing
  Set ws = Nothing
  
  Set emailItem = Nothing
  Set emailApplication = Nothing

End Sub

To prevent typos from ruining days and weeks of work 'Option Explicit' is NEEDED at the top of each code module. This prevents errors caused by missspellings and FORCES every variable to be DECLARED (e.g. Dim i as Integer). http://www.cpearson.com/excel/DeclaringVariables.aspx

Lewis