plz help with macro

N

nisarahmed86

The macro should send and email using outlook.

The recipients will be in column A

The email needs to include in the body the info in column B, C , D and
E

However, if the recipient is repeated I don´t want to send more than
one email.

Example:

ColumnA - ColumnB - ColumnC-COL D -COL E
[email protected] - James - Married - yes - june 1st
[email protected] - Susan - Married
[email protected] - Peter - Married
[email protected] - John - Single
[email protected] - Antonio - Single
[email protected] - Marcia - Divorced


REsult:
one email sent to [email protected] that inccludes in the body
James - Married
Susan - Married

one email sent to [email protected] that includes in the body
Peter - Married

one email sent to [email protected] that includes in the body
John - Single
Antonio - Single
Marcia - Divorced
 
R

Ron de Bruin

See my site for example code

For example this one
http://www.rondebruin.nl/mail/folder3/message.htm

If you need more help post back


--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


The macro should send and email using outlook.

The recipients will be in column A

The email needs to include in the body the info in column B, C , D and
E

However, if the recipient is repeated I don´t want to send more than
one email.

Example:

ColumnA - ColumnB - ColumnC-COL D -COL E
[email protected] - James - Married - yes - june 1st
[email protected] - Susan - Married
[email protected] - Peter - Married
[email protected] - John - Single
[email protected] - Antonio - Single
[email protected] - Marcia - Divorced


REsult:
one email sent to [email protected] that inccludes in the body
James - Married
Susan - Married

one email sent to [email protected] that includes in the body
Peter - Married

one email sent to [email protected] that includes in the body
John - Single
Antonio - Single
Marcia - Divorced
 
C

codehelp

See my site for example code

For example this onehttp://www.rondebruin.nl/mail/folder3/message.htm

If you need more help post back

--

Regards Ron de Bruinhttp://www.rondebruin.nl/tips.htm


The macro should send and email using outlook.

The recipients will be in column A

The email needs to include in the body the info in column B, C , D and
E

However, if the recipient is repeated I don´t want to send more than
one email.

Example:

ColumnA - ColumnB - ColumnC-COL D -COL E
[email protected] - James - Married - yes - june 1st
[email protected] - Susan - Married
[email protected] - Peter - Married
[email protected] - John - Single
[email protected] - Antonio - Single
[email protected] - Marcia - Divorced

REsult:
one email sent to [email protected] that inccludes in the body
James - Married
Susan - Married

one email sent to [email protected] that includes in the body
Peter - Married

one email sent to [email protected] that includes in the body
John - Single
Antonio - Single
Marcia - Divorced
Click to expand...

HEY RON thnks FOR reply thing is in ur code the body of the message is
HARDCODED like DEAR (previous cell value = NAME).... i m new at this
can u plz tell me how to change the code so that the body of mail
includes the cells B to D of that particular email /row.. plz reply
THNKS
 
R

Ron de Bruin

Hi codehelp

The example loop through all mail addresses in B in my example

.Body = "Dear " & cell.Offset(0, -1).Value & vbNewLine & vbNewLine & _
cell.Offset(0, 1).Value & " " cell.Offset(0, 2).Value

This will display

Dear A value
C value & " " & D value

Offset use (Row, column)
So cell.Offset(0, 2).Value
will go two cells to the right from Cell (this is column B) and display the value in D


--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


See my site for example code

For example this onehttp://www.rondebruin.nl/mail/folder3/message.htm

If you need more help post back

--

Regards Ron de Bruinhttp://www.rondebruin.nl/tips.htm


The macro should send and email using outlook.

The recipients will be in column A

The email needs to include in the body the info in column B, C , D and
E

However, if the recipient is repeated I don´t want to send more than
one email.

Example:

ColumnA - ColumnB - ColumnC-COL D -COL E
[email protected] - James - Married - yes - june 1st
[email protected] - Susan - Married
[email protected] - Peter - Married
[email protected] - John - Single
[email protected] - Antonio - Single
[email protected] - Marcia - Divorced

REsult:
one email sent to [email protected] that inccludes in the body
James - Married
Susan - Married

one email sent to [email protected] that includes in the body
Peter - Married

one email sent to [email protected] that includes in the body
John - Single
Antonio - Single
Marcia - Divorced
Click to expand...

HEY RON thnks FOR reply thing is in ur code the body of the message is
HARDCODED like DEAR (previous cell value = NAME).... i m new at this
can u plz tell me how to change the code so that the body of mail
includes the cells B to D of that particular email /row.. plz reply
THNKS
 
C

codehelp

Hi codehelp

The example loop through all mail addresses in B in my example

.Body = "Dear " & cell.Offset(0, -1).Value & vbNewLine & vbNewLine & _
cell.Offset(0, 1).Value & " " cell.Offset(0, 2).Value

This will display

Dear A value
C value & " " & D value

Offset use (Row, column)
So cell.Offset(0, 2).Value
will go two cells to the right from Cell (this is column B) and display the value in D

--

Regards Ron de Bruinhttp://www.rondebruin.nl/tips.htm














HEY RON thnks FOR reply thing is in ur code the body of the message is
HARDCODED like DEAR (previous cell value = NAME).... i m new at this
can u plz tell me how to change the code so that the body of mail
includes the cells B to D of that particular email /row.. plz reply
THNKS- Hide quoted text -

- Show quoted text -
Click to expand...

HEY thnks alot buddy will try it and will post back if any more help
needed .. hey one more thing can u suggest something for that if
repition of email then send single email with combined body in it??
 
R

Ron de Bruin

There is a example on the page check for duplicate addresses
http://www.rondebruin.nl/mail/folder3/message.htm
See the link in example 1

--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


Hi codehelp

The example loop through all mail addresses in B in my example

.Body = "Dear " & cell.Offset(0, -1).Value & vbNewLine & vbNewLine & _
cell.Offset(0, 1).Value & " " cell.Offset(0, 2).Value

This will display

Dear A value
C value & " " & D value

Offset use (Row, column)
So cell.Offset(0, 2).Value
will go two cells to the right from Cell (this is column B) and display the value in D

--

Regards Ron de Bruinhttp://www.rondebruin.nl/tips.htm














HEY RON thnks FOR reply thing is in ur code the body of the message is
HARDCODED like DEAR (previous cell value = NAME).... i m new at this
can u plz tell me how to change the code so that the body of mail
includes the cells B to D of that particular email /row.. plz reply
THNKS- Hide quoted text -

- Show quoted text -
Click to expand...

HEY thnks alot buddy will try it and will post back if any more help
needed .. hey one more thing can u suggest something for that if
repition of email then send single email with combined body in it??
 
C

codehelp

There is a example on the page check for duplicate addresseshttp://www.rondebruin.nl/mail/folder3/message.htm
See the link in example 1

--

Regards Ron de Bruinhttp://www.rondebruin.nl/tips.htm













HEY thnks alot buddy will try it and will post back if any more help
needed .. hey one more thing can u suggest something for that if
repition of email then send single email with combined body in it??- Hide quoted text -

- Show quoted text -
Click to expand...

hey RON could u plz explain wat that code does, like where does it
detect for duplicate email and can anything be done if it gets
duplicate email like for two same emails with different bodies i
would like to send a single email with the 2 body in it.
plz explain wat does this do : Set Addresslist = New
Scripting.Dictionary
and Addresslist.Add cell.Value, cell.Value
and Set olMail = olApp.CreateItem(olMailItem)
thnks alot my friend u are a LIFE SAVER
 
R

Ron de Bruin

Addresslist.Add cell.Value, cell.Value
It will try to add the address to the Addresslist and if it already exist it errors and no mail will be created
Set olMail = olApp.CreateItem(olMailItem)
Click to expand...
It create a new mail

If there are duplicate addresses then there is more work and you need more code
one way is to make a unique list with advanced filter and loop through all unique values and filter on this value.

I do it on this page to create different sheets or workbooks for every unique value
http://www.rondebruin.nl/copy5.htm


--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


There is a example on the page check for duplicate addresseshttp://www.rondebruin.nl/mail/folder3/message.htm
See the link in example 1

--

Regards Ron de Bruinhttp://www.rondebruin.nl/tips.htm













HEY thnks alot buddy will try it and will post back if any more help
needed .. hey one more thing can u suggest something for that if
repition of email then send single email with combined body in it??- Hide quoted text -

- Show quoted text -
Click to expand...

hey RON could u plz explain wat that code does, like where does it
detect for duplicate email and can anything be done if it gets
duplicate email like for two same emails with different bodies i
would like to send a single email with the 2 body in it.
plz explain wat does this do : Set Addresslist = New
Scripting.Dictionary
and Addresslist.Add cell.Value, cell.Value
and Set olMail = olApp.CreateItem(olMailItem)
thnks alot my friend u are a LIFE SAVER
 
Top