Solved

Send amil to a user with many in CC.Need to automate the process.

Posted on 2007-11-26
22
313 Views
Last Modified: 2012-06-27
Hi,

Need to send mails every day to many users for their Birthdays.What is the best way of doing this.This is sent to the user in the To and the whole staff that are in a group in the CC.
Is there a easy way of doing this.Every user has to get invividual mails and the whole staff in Cc.Name has to be as

Hi username
and the user in the To tab.
The others in the CC
The mail has backgroud and pictures in it. Any way to automate this or any better ideas are appretiated.

Any help with this.

Regards
Sharath
0
Comment
Question by:bsharath
  • 14
  • 5
  • 3
22 Comments
 
LVL 76

Expert Comment

by:David Lee
ID: 20355457
Hi, Sharath.

How do you keep track of their birthdays, and will all messages be cc'd to the same group of people?  If not, how can we tell who is cc'd on which messages?
0
 
LVL 11

Author Comment

by:bsharath
ID: 20355476
I have a excel that has
Colum A Name and Colum B the date in MM/DD/YYY format
Yes all messages will be Cc'd to the same group always just the names will differ
0
 
LVL 11

Author Comment

by:bsharath
ID: 20387216
Any help....
0
 
LVL 76

Expert Comment

by:David Lee
ID: 20400367
Try this.  It will mail a birthday message to every entry in the spreadsheet.  We can modify it to only send messages to birthdays matching today's date or to those within a certain number of days of today.
Sub SendBirthdayMessage()

    Dim excApp As Object, _

        excBook As Object, _

        excSheet As Object, _

        olkMessage As Outlook.MailItem, _

        intRow As Integer

    Set excApp = CreateObject("Excel.Application")

    'Change the file name and path on the following line as needed

    Set excBook = excApp.Workbooks.Open("C:\eeTesting\Birthdays.xls")

    Set excSheet = excBook.Worksheets(1)

    intRow = 1

    Do Until excSheet.Cells(intRow, 1) = ""

        Set olkMessage = Application.CreateItem(olMailItem)

        With olkMessage

            .Recipients.Add excSheet.Cells(intRow, 1)

            .Recipients.ResolveAll

            'Change the subject line as needed

            .Subject = "Happy Birthday"

            'Insert your HTML on the following line.  You can insert the HTML directly or read it in from a file.  The HTML can be anything you want it to be.

            .HTMLBody = "Your HTML goes here"

            .Send

        End With

        Set olkMessage = Nothing

    Loop

    excBook.Close False

    Set excSheet = Nothing

    Set excBook = Nothing

    Set excApp = Nothing

    MsgBox "All done.", vbOKOnly + vbInformation, "Send Birthday Greetings"

End Sub

Open in new window

0
 
LVL 11

Author Comment

by:bsharath
ID: 20400550
How should the data in the excel be.
Can you modify according to this Question please...
http://www.experts-exchange.com/Programming/Languages/Q_22997456.html
0
 
LVL 11

Author Comment

by:bsharath
ID: 20400584
0
 
LVL 76

Expert Comment

by:David Lee
ID: 20400604
Well, according to what you posted in this question the data would be in the format

Name        Birthday
Sharath    12/12/1967

If you want to change it to this format

Bdate Date            Username    Email Address        Groupname    Username/Groupname
12/12/1967           Sharath        Sharath@plc.com   Group1          User1/group1

then use the code below
Sub SendBirthdayMessage()

    Dim excApp As Object, _

        excBook As Object, _

        excSheet As Object, _

        olkMessage As Outlook.MailItem, _

        intRow As Integer

    Set excApp = CreateObject("Excel.Application")

    'Change the file name and path on the following line as needed

    Set excBook = excApp.Workbooks.Open("C:\eeTesting\Birthdays.xls")

    Set excSheet = excBook.Worksheets(1)

    intRow = 1

    Do Until excSheet.Cells(intRow, 1) = ""

        If CDate(excSheet.Cells(intRow, 1) = Date() Then

            Set olkMessage = Application.CreateItem(olMailItem)

            With olkMessage

                .Recipients.Add excSheet.Cells(intRow, 3)

                .Recipients.ResolveAll

                .CC = excSheet.Cells(intRow, 4)

                'Change the subject line as needed

                .Subject = "Happy Birthday"

                'Insert your HTML on the following line.  You can insert the HTML directly or read it in from a file.  The HTML can be anything you want it to be.

            .HTMLBody = "Your HTML goes here"

            .Send

        End With

        Set olkMessage = Nothing

    Loop

    excBook.Close False

    Set excSheet = Nothing

    Set excBook = Nothing

    Set excApp = Nothing

    MsgBox "All done.", vbOKOnly + vbInformation, "Send Birthday Greetings"

End Sub

Open in new window

0
 
LVL 11

Author Comment

by:bsharath
ID: 20400649
Is this a outlook macro?
I get a compile error here
        If CDate(excSheet.Cells(intRow, 1) = Date() Then

Is a Bracket missing?
0
 
LVL 11

Author Comment

by:bsharath
ID: 20400652
For both the questions its the same results that i need.
1. Automate the process in outlook to find the date and if the date is todays date then send a mail to all the bday members with some users in CC.
0
 
LVL 76

Expert Comment

by:David Lee
ID: 20400664
Oops, sorry.  Yes, it is an Outlook macro and yes a parenthesis is missing.  The line should read

    If CDate(excSheet.Cells(intRow, 1)) = Date() Then
0
 
LVL 11

Author Comment

by:bsharath
ID: 20400686
I get this

---------------------------
Microsoft Visual Basic
---------------------------
Compile error:

Loop without Do
---------------------------
OK   Help  
---------------------------
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 76

Accepted Solution

by:
David Lee earned 500 total points
ID: 20407519
Oops.  Try this.
Sub SendBirthdayMessage()

    Dim excApp As Object, _

        excBook As Object, _

        excSheet As Object, _

        olkMessage As Outlook.MailItem, _

        intRow As Integer

    Set excApp = CreateObject("Excel.Application")

    'Change the file name and path on the following line as needed

    Set excBook = excApp.Workbooks.Open("C:\eeTesting\Birthdays.xls")

    Set excSheet = excBook.Worksheets(1)

    intRow = 1

    Do Until excSheet.Cells(intRow, 1) = ""

        If CDate(excSheet.Cells(intRow, 1) = Date() Then

            Set olkMessage = Application.CreateItem(olMailItem)

            With olkMessage

                .Recipients.Add excSheet.Cells(intRow, 3)

                .Recipients.ResolveAll

                .CC = excSheet.Cells(intRow, 4)

                'Change the subject line as needed

                .Subject = "Happy Birthday"

                'Insert your HTML on the following line.  You can insert the HTML directly or read it in from a file.  The HTML can be anything you want it to be.

                .HTMLBody = "Your HTML goes here"

                .Send

            End With

        End If

        Set olkMessage = Nothing

    Loop

    excBook.Close False

    Set excSheet = Nothing

    Set excBook = Nothing

    Set excApp = Nothing

    MsgBox "All done.", vbOKOnly + vbInformation, "Send Birthday Greetings"

End Sub

Open in new window

0
 
LVL 11

Author Comment

by:bsharath
ID: 20408623
I get a Run time error 13
in this line when clicked debug
 If CDate(excSheet.Cells(intRow, 1)) = Date Then
0
 
LVL 11

Author Comment

by:bsharath
ID: 20447568
Any help...
0
 
LVL 11

Author Comment

by:bsharath
ID: 20490074
Any help...
0
 
LVL 11

Author Comment

by:bsharath
ID: 20524592
David if time permits plz have a look on this...
0
 
LVL 12

Expert Comment

by:chandru_sol
ID: 21506562
Sharath,

Hope you are doing good.

Did you get any update on this?

regards
Chandru
0
 
LVL 11

Author Comment

by:bsharath
ID: 21506645
Hi Chandru long time....i am fine how are you...

>>Did you get any update on this?
Sorry could not follow
0
 
LVL 12

Expert Comment

by:chandru_sol
ID: 21523050
i was out of country for a project and a bit busy.

Did you get this birthday greetings working?

Chandru
0
 
LVL 11

Author Comment

by:bsharath
ID: 21523079
Not exactly chandru i get the Runtime error.
0
 
LVL 11

Author Comment

by:bsharath
ID: 21523080
Not exactly chandru i get the Runtime error.
0
 
LVL 12

Expert Comment

by:chandru_sol
ID: 21523111
i would also require this for birthday wishes. I am not sure about macros in excel.
0

Featured Post

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

When we want to run, execute or repeat a statement multiple times, a loop is necessary. This article covers the two types of loops in Python: the while loop and the for loop.
Resolve DNS query failed errors for Exchange
The viewer will learn how to pass data into a function in C++. This is one step further in using functions. Instead of only printing text onto the console, the function will be able to perform calculations with argumentents given by the user.
The viewer will be introduced to the technique of using vectors in C++. The video will cover how to define a vector, store values in the vector and retrieve data from the values stored in the vector.

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now