Email Automation from EXCEL 2010

Nandakumar Balakrishnan
Nandakumar Balakrishnan used Ask the Experts™
on
Hi Experts,

Kindly help me on the below mentioned requirement .

There is an excel file which contains task list of every users and their action and date in excel. Date had to be read and send email to the users with their tasks. Excel file will be located in network drive which is common to users.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
I think you need to do this manually.. you can schedule you mail send date in outlook ..
Commented:
Yes. This is possible. You can do this from vba script. Have a button in your worksheet. Write a function to read the cells on clicking the button and send it.

Here you go with a good example.

http://mrspreadsheets.com/1/post/2013/09/vba-code-snippet-23-create-email-from-excel-with-file-attachment.html
One question.. why you need to do it in excel where you can do this is outlook without writing any code.
You could do it by automating Outlook as Gottler suggested. My personal preference is to stay away from Outlook whenever possible, instead I use CDO.

This example is for sending mail from your gmail.com account, you can use any smtp server.
http://www.rondebruin.nl/win/s1/cdo.htm

'If you have a GMail account then you can try this example to use the GMail smtp server
'The example will send a small text message
'You must change four code lines before you can test the code

'.Item("http://schemas.microsoft.com/cdo/configuration/sendusername") = "Full GMail mail address"
'.Item("http://schemas.microsoft.com/cdo/configuration/sendpassword") = "GMail password"

'Use your own mail address to test the code in this line
'.To = "Mail address receiver"

'Change YourName to the From name you want to use
'.From = """YourName"" <Reply@something.com>"

Sub CDO_Mail_Small_Text_2()
    Dim iMsg As Object
    Dim iConf As Object
    Dim strbody As String
    Dim Flds As Variant

    Set iMsg = CreateObject("CDO.Message")
    Set iConf = CreateObject("CDO.Configuration")

    iConf.Load -1    ' CDO Source Defaults
    Set Flds = iConf.Fields
    With Flds
        .Item("http://schemas.microsoft.com/cdo/configuration/smtpusessl") = True
        .Item("http://schemas.microsoft.com/cdo/configuration/smtpauthenticate") = 1
        .Item("http://schemas.microsoft.com/cdo/configuration/sendusername") = "Full GMail mail address"
        .Item("http://schemas.microsoft.com/cdo/configuration/sendpassword") = "GMail password"
        .Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") = "smtp.gmail.com"
        .Item("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2
        .Item("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 465
        .Update
    End With

    strbody = "Hi there" & vbNewLine & vbNewLine & _
              "This is line 1" & vbNewLine & _
              "This is line 2" & vbNewLine & _
              "This is line 3" & vbNewLine & _
              "This is line 4"

    With iMsg
        Set .Configuration = iConf
        .To = "Mail address receiver"
        .CC = ""
        .BCC = ""
        ' Note: The reply address is not working if you use this Gmail example
        ' It will use your Gmail address automatic. But you can add this line
        ' to change the reply address  .ReplyTo = "Reply@something.com"
        .From = """YourName"" <Reply@something.com>"
        .Subject = "Important message"
        .TextBody = strbody
        .Send
    End With

End Sub

Open in new window


If you upload a sample file, I will customize the code to do exactly what you want.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial