Dear EE,
Dear Ashish,
Its really helpful for me to understand the VBA.
My requirement is 85% similar but I also want to add some selective
greeting picture in body text after massage and before signature and
also want to sent the mail thru excel with Permission “Do not forward”
and “Do not Reply”.
Please suggest.
Thanks,
Manish
---------- Forwarded message ----------
From: ashish koul <koul.ash...@gmail.com>
Date: Aug 21 2010, 3:27 pm
Subject: $$Excel-Macros$$ sending e mail based on data in column in
excel
To: MS EXCEL AND VBA MACROS
in reference select Microsoft outlook library
Sub bdaymessages()
Dim I, K As Long
Dim olApp As Outlook.Application
Dim olMail As MailItem
Dim SigString As String
Dim Signature As String
Application.ScreenUpdating = False
Set olApp = New Outlook.Application
'it is counting the non blank cells in col a
K =
Application.WorksheetFunction.CountA(Sheets("Sheet1").Range("a:a"))
For I = 2 To K
If Day(Now) = Day(CDate(Range("c" & I).Value)) And Month(Now) =
Month(CDate(Range("c" & I).Value)) Then
Set olMail = olApp.CreateItem(olMailItem)
With olMail
.To = ActiveSheet.Range("b" & I).Text
.Subject = "Happy Birthday Dear " & Range("a" & I).Text
.Body = "Dear " & ActiveSheet.Range("a" & I).Text & vbCrLf &
vbCrLf
& " birthday message " & vbCrLf & ActiveSheet.Range("e" & I).Text
.Send
End With
Set olMail = Nothing
End If
If (Year(Now) - Year(CDate(Range("D" & I).Value))) Mod 5 = 0 Then
Set olMail = olApp.CreateItem(olMailItem)
With olMail
.To = ActiveSheet.Range("b" & I).Text
.Subject = "Congratulations on completion of " & Year(Now) -
Year(CDate(Range("D" & I).Value)) & " years of service"
.Body = "Dear " & ActiveSheet.Range("a" & I).Text & vbCrLf &
vbCrLf
& " Congrats message " & vbCrLf & ActiveSheet.Range("e" & I).Text
.Send
End With
Set olMail = Nothing
End If
Next I
Set olApp = Nothing
Application.ScreenUpdating = True
End Sub
Regards
Ashish koulhttp://akoul.blogspot.com/
On Sat, Aug 21, 2010 at 2:09 PM, Chandra Gupt Kumar <
kumar.bemlmum...@gmail.com> wrote:
> Hey I have copied and pasted in new module, but it is showing compile
> error. If you don’t mind, could u please attach the code in notepad.
> Regards,
> C.G.Kumar
> *From:* excel-macros@googlegroups.com [mailto:
> excel-macros@googlegroups.com] *On Behalf Of *ashish koul
> *Sent:* Saturday, August 21, 2010 12:59 PM
> *To:* excel-macros@googlegroups.com
> *Subject:* Re: $$Excel-Macros$$ sending e mail based on data in column in
> excel
> Sub bdaymessages()
> Dim I, K As Long
> Dim olApp As Outlook.Application
> Dim olMail As MailItem
> Dim SigString As String
> Dim Signature As String
> Application.ScreenUpdating = False
> Set olApp = New Outlook.Application
> 'it is counting the non blank cells in col a
> K = Application.WorksheetFunction.
> CountA(Sheets("Sheet1").Range("a:a"))
> For I = 2 To K
> If Day(Now) = Day(CDate(Range("c" & I).Value)) And Month(Now) =
> Month(CDate(Range("c" & I).Value)) Then
> Set olMail = olApp.CreateItem(olMailItem)
> With olMail
> .To = ActiveSheet.Range("b" & I).Text
> .Subject = "Happy Birthday Dear " & Range("a" & I).Text
> .Body = "Dear " & ActiveSheet.Range("a" & I).Text & vbCrLf &
> vbCrLf & " birthday message " & vbCrLf & ActiveSheet.Range("e" & I).Text
> .Display
> '.Send
> End With
> Set olMail = Nothing
> End If
> If (Year(Now) - Year(CDate(Range("D" & I).Value))) Mod 5 = 0 Then
> Set olMail = olApp.CreateItem(olMailItem)
> With olMail
> .To = ActiveSheet.Range("b" & I).Text
> .Subject = "Congratulations on completion of " & Year(Now) -
> Year(CDate(Range("D" & I).Value)) & " years of service"
> .Body = "Dear " & ActiveSheet.Range("a" & I).Text & vbCrLf &
> vbCrLf & " Congrats message " & vbCrLf & ActiveSheet.Range("e" & I).Text
> .Display
> '.send
> End With
> Set olMail = Nothing
> End If
> Regards
> Ashish koul
>http://akoul.blogspot.com/
> On Sat, Aug 21, 2010 at 10:31 AM, Chandra Gupt Kumar <
> kumar.bemlmum...@gmail.com> wrote:
> Could you please share how it is possible.
> Regards,
> C.G.Kumar
> *From:* excel-macros@googlegroups.com [mailto:
> excel-macros@googlegroups.com] *On Behalf Of *Vasant
> *Sent:* Friday, August 20, 2010 1:43 PM
> *To:* excel-macros@googlegroups.com
> *Subject:* Re: $$Excel-Macros$$ sending e mail based on data in column in
> excel
> Yes, it is possible to do
> Regards
> Vasant
> On 8/20/10, *Chandra Gupt Kumar* <kumar.bemlmum...@gmail.com> wrote:
> Dear All,
> My requirements are as follows;
> 1. Based on date of birth (Col D), mail to be sent on particular day
> to respective office for good wishes.
> 2. Based on date of joining , congratulation mail to be sent if the
> concerned completes 5,10,15,20,25,35,40 years of service.
> It is possible to do so ?
> Regards,
> C.G.Kumar
> --
> ----------------------------------------------------------------------------------
> Some important links for excel users:
> 1. Follow us on TWITTER for tips tricks and links :
>http://twitter.com/exceldailytip
> 2. Join our LinkedIN group @http://www.linkedin.com/groups?gid=1871310
> 3. Excel tutorials athttp://www.excel-macros.blogspot.com
> 4. Learn VBA Macros athttp://www.quickvba.blogspot.com
> 5. Excel Tips and Tricks athttp://exceldailytip.blogspot.com
> To post to this group, send email to excel-macros@googlegroups.com
> <><><><><><><><><><><><><><><><><><><><><><>
> HELP US GROW !!
> We reach over 7000 subscribers worldwide and receive many nice notes about
> the learning and support from the group.Let friends and co-workers know they
> can subscribe to group at
>http://groups.google.com/group/excel-macros/subscribe
> --
> Regards
> Vasant
> --
> ----------------------------------------------------------------------------------
> Some important links for excel users:
> 1. Follow us on TWITTER for tips tricks and links :
>http://twitter.com/exceldailytip
> 2. Join our LinkedIN group @http://www.linkedin.com/groups?gid=1871310
> 3. Excel tutorials athttp://www.excel-macros.blogspot.com
> 4. Learn VBA Macros athttp://www.quickvba.blogspot.com
> 5. Excel Tips and Tricks athttp://exceldailytip.blogspot.com
> To post to this group, send email to excel-macros@googlegroups.com
> <><><><><><><><><><><><><><><><><><><><><><>
> HELP US GROW !!
> We reach over 7000 subscribers worldwide and receive many nice notes about
> the learning and support from the group.Let friends and co-workers know they
> can subscribe to group at
>http://groups.google.com/group/excel-macros/subscribe
> --
> ----------------------------------------------------------------------------------
> Some important links for excel users:
> 1. Follow us on TWITTER for tips tricks and links :
>http://twitter.com/exceldailytip
> 2. Join our LinkedIN group @http://www.linkedin.com/groups?gid=1871310
> 3. Excel tutorials athttp://www.excel-macros.blogspot.com
> 4. Learn VBA Macros athttp://www.quickvba.blogspot.com
> 5. Excel Tips and Tricks athttp://exceldailytip.blogspot.com
> To post to this group, send email to excel-macros@googlegroups.com
> <><><><><><><><><><><><><><><><><><><><><><>
> HELP US GROW !!
> We reach over 7000 subscribers worldwide and receive many nice notes about
> the learning and support from the group.Let friends and co-workers know they
> can subscribe to group at
>http://groups.google.com/group/excel-macros/subscribe
> --
> ----------------------------------------------------------------------------------
> Some important links for excel users:
> 1. Follow us on TWITTER for tips tricks and links :
>http://twitter.com/exceldailytip
> 2. Join our LinkedIN group @http://www.linkedin.com/groups?gid=1871310
> 3. Excel tutorials athttp://www.excel-macros.blogspot.com
> 4. Learn VBA Macros athttp://www.quickvba.blogspot.com
> 5. Excel Tips and Tricks athttp://exceldailytip.blogspot.com
> To post to this group, send email to excel-macros@googlegroups.com
> <><><><><><><><><><><><><><><><><><><><><><>
> HELP US GROW !!
> We reach over 7000 subscribers worldwide and receive many nice notes about
> the learning and support from the group.Let friends and co-workers know they
> can subscribe to group at
>http://groups.google.com/group/excel-macros/subscribe
bday wishes.txt
2KViewDownload
birthday wishes.xlsm
26KViewDownload- Hide quoted text -
- Show quoted text -
--
----------------------------------------------------------------------------------
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links :
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
To post to this group, send email to excel-macros@googlegroups.com
<><><><><><><><><><><><><><><><><><><><><><>
Like our page on facebook , Just follow below link
http://www.facebook.com/discussexcel
