Need to Send bulk Mails

[Ananthan]

Hi Team,

I would like to know can we send mails from an excel file which contains mail id, customer,name, amount & branch from where he obtained our service through mail merge or by using macros ? I am using windows live mail not outlook.

Regards
Ananthan

[Rudi]

Ron de Bruin has some very good reference code for emailing workbooks and sheets from Excel.

See this page for emailing options.
And this page for emailing workbooks, especially Example 2.

Note: This post references this post...

I'm thinking that since the macro I set up in the previous post saves the workbooks as separate files, the example 2 macro can be integrated into the code to email the workbook after it is created. Something like:

Instead of these lines in the macro:

Code: Select all

            ActiveWorkbook.SaveAs ThisWorkbook.Path & "/" & Range("A2").Value & ".xlsx", FileFormat:=xlOpenXMLWorkbook
            ActiveWorkbook.Close False

You can substitute it with:

Code: Select all

'email = get address from cell range...
Call Mail_Workbook_2

The other alternative is to email the entire workbook at the point of creating it, so there is no need to save it to the drive.

I don't have much experience with emailing files, and especially not with Windows Live Mail, so I hope another user will be able to assist you here...

[Ananthan]

Hi,
I just want to send mails from an excel file using macros.

For Eg: the mail will be like this

Dear "Name",

Geerings from "Company Name",

We are happy to inform you that the "item No" you have taken from our branch is falling due on "date", Please visit the branch to prolong our relationship further or sms to "Number".Our executive will contact you.

Reagrds
ABC Ltd

[Rudi]

Is this request connected to the previous post where the Branches needed to be split into files or is this a different file with new requirements?

[Ananthan]

Both are different this is just send mails, meantime we are sending sms also to remind them.

[HansV]

As in your previous thread, please provide more detailed information.

1) Do you have column headers (field names) in row 1?
2) Which column contains the e-mail address?
3) Which column contains the "Name"
4) Which column contains the "Company Name"?
5) Which column contains the "Item No"
6) Which column contains the "Date"
7) Which column contains the "Number"?

[Ananthan]

Please find the answers below:-

1)Yes
2)Second column
3)First column
4)Company Name is common & it will be in the last column
5)Third column
6)Forth column
7)Fifth column

Simply it reads like this
Name,email address,item number,amount, date,number & company name in he last.

Dear "Name",

Geerings from "Company Name",

We are happy to inform you that the "item No" you have taken from our branch of "amount "is falling due on "date", Please visit the branch to prolong our relationship further or sms to "Number".Our executive will contact you.

Reagrds
ABC Ltd

[HansV]

Since Windows Live Mail does not provide a programming interface, we cannot create a macro to send e-mail messages with variable body text from Excel. You'd need to use Outlook for that.
(The examples by Ron de Bruin that Rudi pointed to will send a workbook or part of it as an attachment to the e-mail message)
If Windows Live Mail is your default e-mail program, you can probably use mail merge to e-mail from Word, with your Excel workbook as data source. Once you have set up the mail merge, you can execute it repeatedly.

[Ananthan]

Ok done thanks, Please provide me the procedure if it can be done through outlook mail.

Regards
Ananthan

[HansV]

Here is such a macro. It will work best if you have already started Outlook before running it.
I have assumed that the amount (which you hadn't mentioned originally) is in column F and the company name in column G. You can change that in the macro if needed.

Code: Select all

Sub SendEmail()
    Dim r As Long
    Dim m As Long
    Dim objOL As Object
    Dim objMsg As Object
    Dim blnStart As Boolean

    ' Try to open Outlook
    On Error Resume Next
    Set objOL = GetObject(Class:="Outlook.Application")
    If objOL Is Nothing Then
        Set objOL = CreateObject(Class:="Outlook.Application")
        If objOL Is Nothing Then
            MsgBox "Failed to start Outlook!", vbCritical
            Exit Sub
        End If
        blnStart = True
    End If
    On Error GoTo ErrHandler
    objOL.Session.Logon

    ' Determine last used row
    m = Range("B" & Rows.Count).End(xlUp).Row
    ' Loop through the rows
    For r = 2 To m
        ' Check if e-mail address is filled in
        If Range("B" & r).Value <> "" Then
            ' Create message
            Set objMsg = objOL.CreateItem(0) ' olMailItem
            ' Recipient
            objMsg.Recipients.Add Range("B" & r)
            ' Subject
            objMsg.Subject = "Reminder"
            ' Body
            objMsg.Body = "Dear " & Range("A" & r).Value & "," & vbCrLf & vbCrLf & _
                "Greetings from " & Range("G" & r).Value & "," & vbCrLf & vbCrLf & _
                "We are happy to inform you that the " & Range("C" & r).Value & _
                " you have taken from our branch of " & Range("F" & r).Text & _
                " is falling due on " & Range("D" & r).Text & _
                ". Please visit the branch to prolong our relationship or SMS to " & _
                Range("E" & r).Text & ". Our executive will contact you." & vbCrLf & vbCrLf & _
                "Regards," & vbCrLf & "ABC Ltd"
            ' Use only one of the next two instructions
            ' For testing: display the message
            objMsg.Display
            ' For the final version: send the message
            'objMsg.Send
        End If
    Next r

ExitHandler:
    On Error Resume Next
    If blnStart And Not objOL Is Nothing Then
        objOL.Quit
    End If
    Exit Sub

ErrHandler:
    MsgBox Err.Description, vbExclamation
    Resume ExitHandler
End Sub

[Ananthan]

Thanks a lot

If I need to change any column/rows, do I need to change the coding accordingly, Please just give me a brief.
Or I can send the same based on the given code.

Regards
Ananthan

[Ananthan]

Please find the corrected one,

Dear "Name",

Geerings from "Company Name",

We are happy to inform you that the "item No" you have taken from our "branch" of "amount "is falling due on "date", Please visit the branch to prolong our relationship further or sms to "Number".Our executive will contact you.

1) Comapny Name in 'G' column
2) Item No in "C" column
3) Branch in "E" column
4) Amount in "F" column
5) Date in "D" column
6) Number in "H" Column

Please guide me either I can alter the given code Or need to create a new code.....

Regards
Ananthan

[HansV]

The code automatically loops through all used rows from row 2 down. If you want to start in another row than in row 2, change the 2 in the line

Code: Select all

    For r = 2 To m

I added lots of comments to the code, and in the parts that create the body (the message text), it should be obvious from the wording what each cell is used for.
For example, in the line

Code: Select all

            objMsg.Body = "Dear " & Range("A" & r).Value & "," & vbCrLf & vbCrLf & _

you can see that Range("A" & r).Value is used for the "Name". If "Name" is in another column than in column A, change the code accordingly.
In case you didn't know: vbCrLf inserts a paragraph break into the message.

[Ananthan]

Hi , I am unable to send mails this is not working properly
Shows error msg like "failed to start outlook"
Dear Sir/Madam,

Greetings from ABC Ltd,

We are happy to inform you that the policy No.1234 that you have taken from our "Mumbai" branch is falling due on 01.05.2014, Please visit the branch to renew the policy further or please call/SMS to 12345, our executive will contact you.


Thanks for all the support.

Regards
ABC Ltd

[HansV]

If you get an error message "Failed to start Outlook", Outlook was not available on the computer where you ran the code. The code will only work if Outlook is available.

[Ananthan]

Thanks a ton I ts working only with MS outlook not with the outlook express......

Regards
Ananthan

[HansV]

The code will not work with Outlook Express because Outlook Express does not provide a programming interface.