automatic birthday wishes using excel and outlook.

[nidhinkg]

i have a excel file which contains Name(column A),DOB(column B), & email address(column C),.

i would like to send birthday wishes mail automatically to the people according to their bday.

there may be "n" number of people on a particular day. i would like to send a single mail to all the people.i am using microsoft outlook.

When i open that excel file, it should pop up the New Message window of outlook with all email ids in the To. field with "Happy B'day" as subject and Message body as :"many more happy returns of the day & have a nice n wonderful day ahead".



i am very new to excel n have no idea about VB also. pls pls pls help me.....

nidhin.

 

[daiwuliz]

'Run-time error '
We need to know who to send this to. Make sure you enter at least one name' message pops up.

 

[Worf]

Do you have mail addresses in column C? Is the appropriate message field populated with a valid mail address?
Try to send one email manually to see if it works, and compare it with the code-generated window.

 

[a2sovolunteers]

This was so helpful and I got it working right away. THANK YOU! Is there any way to have it send an HTML e-mail with an image and a little more flash to it than a basic text?

 

[DelusionalTuck]

Ok so I tried to use this example as a guide. I am trying to send an email to my customer after I save it as a pdf.

Here is my code.

Sub SendEmail()

Dim olApp As Outlook.Application
Dim MItem As Outlook.MailItem
Dim cell As Range
Dim Subj As String
Dim EmailAddr As String
Dim Msg As String
Dim wb As Workbook
Dim ws As Worksheet
Dim rng As Range
Set olApp = New Outlook.Application
Application.ScreenUpdating = False
Set wb = Workbooks.Open( _
"C:\Users\Delusional Customer\Desktop\Delusional Invoices v2.xlsx")
Set ws = Worksheets("Invoice")
 
With rng
    FName = cell("G4").Value
    Subj = "Here is your New Delusional Designs CNC Invoice"
    EmailAddr = cell("Q6").Value
    Msg = "Dear " & FName & "," & vbNewLine
    Msg = Msg & vbNewLine & " Here is your new invoice from Delusional Designs CNC. Have a wonderful day." & vbCrLf & vbCrLf

    Set MItem = olApp.CreateItem(olMailItem)
    With MItem
        .To = EmailAddr
        .CC = "gmuzek@yahoo.com"
        .Subject = Subj
        .Body = Msg
        .Attachments.Add ("C:\Users\Delusional Customer\Desktop\Invoices\Invoice for " & ActiveSheet.Range("G4").Value & ".pdf")
        .Attachments.Add ("C:\Users\Delusional Customer\Desktop\Invoices\DD Payment info.pdf")
        .Send
    End With
End With

Application.ScreenUpdating = True
 
End Sub

Thanks Jim

 

[DelusionalTuck]

Forgot to state where my error is occurring.

It happens in the first with.

thanks again

jim

 

[Worf]

This was so helpful and I got it working right away. THANK YOU! Is there any way to have it send an HTML e-mail with an image and a little more flash to it than a basic text?

' Excel module
Sub Embed_OL()
Dim mainWB As Workbook, SendID, CCID, olMail As MailItem, otlapp As Object
Set otlapp = CreateObject("Outlook.Application")
Set olMail = otlapp.CreateItem(olMailItem)
Set mainWB = ActiveWorkbook
SendID = mainWB.Sheets("Mail").[b1]
CCID = mainWB.Sheets("Mail").[b2]
With olMail
    .To = SendID
    If CCID <> "" Then .cc = CCID
    .Subject = mainWB.Sheets("Mail").[b3]
    .Attachments.Add "C:\pub\mainshape.jpg", olByValue, 0   ' 0 = hidden
    .HTMLBody = mainWB.Sheets("Mail").[b4] & "
[B][COLOR=#daa520]Embedded Image[/COLOR]:[/B]
" _
                & "[IMG]https://www.mrexcel.com/forum/mainshape.jpg[/IMG]
" _
                & "
Best Regards, 
John"
    .Display
'    .Send
End With
MsgBox ("Your message has been sent to " & SendID)
End Sub

Note: the tags were interpreted, I'll find another way to post it.

 

[Worf]

 

[Worf]

Forgot to state where my error is occurring.

It happens in the first with.
thanks again
jim

Something like this:

Sub SendEmail()
Dim olApp As Outlook.Application, MItem As MailItem
Dim fname$, Msg$, wb As Workbook, ws As Worksheet, mypath$


mypath = "c:\pub\"
Set olApp = New Outlook.Application
Set wb = Workbooks.Open(mypath & "Delusional.xlsx")
Set ws = wb.Worksheets("Invoice")
    fname = ws.[G4]
    Msg = "Dear " & fname & "," & vbNewLine
    Msg = Msg & vbNewLine & " Here is your new invoice from Delusional Designs CNC." _
    & " Have a wonderful day." & vbCrLf & vbCrLf
    Set MItem = olApp.CreateItem(olMailItem)
    With MItem
        .To = ws.[q6]
        .cc = "gmuzek@yahoo.com"
        .Subject = "Here is your New Delusional Designs CNC Invoice"
        .Body = Msg
        .Attachments.Add (mypath & "Invoice for " & fname & ".pdf")
        .Attachments.Add (mypath & "DD Payment info.pdf")
        .Display
    End With
Application.ScreenUpdating = True
End Sub

 

[DelusionalTuck]

Thank you Worf,

It works great just needed to change 2 paths.

Tuck

 

[Worf]

You are welcome and thanks for the feedback.