• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Excel to send email based on Date to more than 1 user

S

Sukesh Nair

New Member
HI ,

I am trying to understanding if we can send a email using VBS in excel .

Challenge is to send an email on the a day which is mapped against the email ID is sheet " Email ID" ( Sample attached)

Eg :- Email should be send to all the email ID's with date mapped as 01/01/2014. as per this example 3 users . So excel should send an email to all 3 users .

Re to sheet "Email Structure , when excel send an email Subject & mail Boby should be taken from the cell .

Is it Possible to create such automated tool.

Regards,
Sukesh Nair
 

Attachments

This is a patchwork i put together for this request (mailsender comes directly from Ron de Bruin's website)

Code: 
Sub AutoSendMail()

  dt = Format(Date, "MMDD")
  Range("A2", Range("A" & Rows.Count).End(xlUp)).Offset(, 2) = "=TEXT(MONTH(B2),""00"") & TEXT(DAY(B2),""00"")"
  Range("C1", Range("C" & Rows.Count).End(xlUp)).AutoFilter 1, dt
 
  NumMatchingDates = ActiveSheet.AutoFilter.Range.SpecialCells(xlCellTypeVisible).Count - 1
  If NumMatchingDates >= 1 Then
  For Each cll In ActiveSheet.AutoFilter.Range.SpecialCells(xlCellTypeVisible).Cells
  If cll.Row > 1 Then mailaddr = mailaddr & cll.Offset(, -2).Value & ";"
  Next
  mailaddr = Left(mailaddr, Len(mailaddr) - 1)
  'Get mail parameters form "Email Structure"
  'call mailsender here
  'mailsender mailaddr, mailbody, mailsubj
  End If

  Range("C1").EntireColumn.Delete
 
End Sub

Sub mailsender(toaddr As String, mailbody As String, mailsubj As String)
'Working in Excel 2000-2013
'This example send the last saved version of the Activeworkbook
'For Tips see: http://www.rondebruin.nl/win/winmail/Outlook/tips.htm
  Dim OutApp As Object
  Dim OutMail As Object

  Set OutApp = CreateObject("Outlook.Application")
  Set OutMail = OutApp.CreateItem(0)

  On Error Resume Next
  With OutMail
  .to = toaddr
  .CC = ""
  .BCC = ""
  .Subject = mailsubj
  .Body = mailbody
  'You can add other files also like this
  '.Attachments.Add ("C:\test.txt")
  .Send  'or use .Display
  End With
  On Error GoTo 0

  Set OutMail = Nothing
  Set OutApp = Nothing
End Sub

Play a bit, you'll need to get body and subjet of the mail from "Email Structure"
 
You must log in or register to reply here.
Back
Top