How to send a mail to some specific members.

[satya12]

Hi All,

I am having "A" Col has mail id's and "B " Col has Date .
If we click on the button on the sheet , the macro will take "A" Col mail id's and In the body the date will be added in the message. is it possible.

 

[mumps]

Assuming that by "mail id's" you mean email addresses, try:

Sub CreateEmails()
    Application.ScreenUpdating = False
    Dim OutApp As Object, OutMail As Object, LastRow As Long, ID As Range
    LastRow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    Set OutApp = CreateObject("Outlook.Application")
    For Each ID In Range("A2:A" & LastRow)
        Set OutMail = OutApp.CreateItem(0)
        With OutMail
            .To = ID.Value
            .Subject = "Date"
            .HTMLBody = ID.Offset(, 1).Value
            .Display
        End With
    Next ID
    Application.ScreenUpdating = True
End Sub

 

[satya12]

Thanks for your help! its working fine , but can you please tell me how to send grp mails and adding user input box in the macro for date .like in the A col we are having 10 mails and B col having some dates different or repeated , in that time macro have to ask which date should be added to the body of the mail.

Mails	Dates
ABC@gamil.com	19th Jan
DEF@gmail.com	20th Jan
GHI@gmail.com	19th Jan

Data will look like this..macro will do take the all the mails at a time, and in the body macro have to ask date which date have been add to mail body , we will give date like 19th jan , after then the mail will send automatically.

 

[mumps]

Try:

Sub CreateEmails()
    Application.ScreenUpdating = False
    Dim OutApp As Object, OutMail As Object, LastRow As Long, ID As Range, d As Date, response As String
    LastRow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    response = InputBox("Enter the date in the format: yyyy/mm/dd")
    If response = "" Then Exit Sub
    Set OutApp = CreateObject("Outlook.Application")
    For Each ID In Range("A2:A" & LastRow)
        Set OutMail = OutApp.CreateItem(0)
        With OutMail
            .To = ID.Value
            .Subject = Date
            .HTMLBody = Format(response, "Mmm dd")
            .Display
        End With
    Next ID
    Application.ScreenUpdating = True
End Sub

Change .Display (in red) to .Send if you want to send the emails instead of displaying them.

 

[satya12]

thanks for your help! its working fine. But it is taking one by one mail from Col "A", but i want All the mails in the Col-"A" will take and send all the mail at a time in one mail

 

[mumps]

Unfortunately, that's not possible. The emails have to be created one at a time.