auto mailer VBA

[ushaanu]

HI all,

I have some data of pending Payment Follow up data, I need to share details auto mailer with pending Payment data. system need to 1st match the mail id , if mail id is same for lines item than consolidate the data and mail to particular id. sample RAW data and required auto mailer enclosed.

Thanks in advance.

Anu

 

[ushaanu]

HI All,

i need one Macro which can use auto mailer of welcome letter. i have one excel file where we can put customer name his email address. it a huge data base so, if possible, we can mark skip Y/N option for mail sending. welcome letter (PDF File ) and mail content will same for all customers. example file .

 

[BobBridges]

Ushaanu, I notice no one's answered this yet. I won't just write you a VBA program, but if you can write it yourself I don't mind helping you, explaining what parts you need in order to talk to both Excel and Outlook. Are you able to learn? How much do you already know? Or which parts do you NOT know?

 

[ushaanu]

thank you soo much for your help . i have one macro code . i just need to add attachment PDF file which user can give path in excel file .

and also need some change in fount stlye bold and color. if possible

code is

>>> use code - tags <<<

Sub Send_Email_with_Signature()

Dim Outlook_App As Object
Dim msg As Object
Dim sign As String
Dim i As Integer
Set Outlook_App = CreateObject("Outlook.Application")
Dim sh As Worksheet
Set sh = ThisWorkbook.Sheets("Data")

For i = 3 To sh.Range("C" & Application.Rows.Count).End(xlUp).Row

If sh.Range("A" & i).Value = "" Then  ''Check Skip
  
    Set msg = Outlook_App.CreateItem(0)
  
    With msg
        .display
    End With
  
    sign = msg.HTMLBody
    With msg
        .To = sh.Range("D" & i).Value
        .CC = sh.Range("E" & i).Value
        .Subject = "Welcome to  Family!" & " " & "" & sh.Range("B" & i) & ""
        .HTMLBody = "<big>Dear <Bold>" & sh.Range("C" & i).Value & "<big><br><br><p style='color:#800000;'>Greetings from  India Private Limited!</p>" & _
        "<p style='color:#800000;'<Bold>Congratulations on bringing your new book! We would like to welcome you to the global family of INdia with an endeavor to 'Delighting you!!'.</p>" & _
        "<p style='color:#000000;'<Midium>We take this opportunity to thank you for trusting us. We are committed to providing you with the best services.</p>" & _
        "<p style='color:#0000FF;'<Midium>For our smooth business association and query that you may have related to service support,  may I request you to go through the attached file of Welcome letter. This will help you to optimum utilization of the product with support from Us.</p>" & _
        "<p style='color:#000000;'<Midium>Thank you again for choosing to do business with us. We are grateful for the opportunity to help you grow your floral business and will work tirelessly to provide you best support.</p>" & _
        "" & _
         sign

        If sh.Range("J1").Value = 1 Then  ''' check option button value
            .send
        Else
            .display
        End If
    End With
  
    Set msg = Nothing

End If

Next i


Set Outlook_App = Nothing

If sh.Range("H1").Value = 1 Then MsgBox "Done"

End Sub

 

[BobBridges]

If you have the email working, attaching a file to it is as easy as the To address and the subject line. Let's see, I have a program around here that adds attachments ... right, here it is.

oeml.Attachments.Add fnf,1 'olByValue (make copy)

In this statement, oeml is the email item; your variable for the same thing is msg. My variable fnf is the full file name, that is, the path and filename with extension. The second argument is the type of attachment; I'm not sure what variations there are, but judging by the comment I added, olByCopy must make a copy of the file and attach it to the email. Maybe another type of attachment sends the file itself, which then disappears from your hard drive, I don't know.

If you're writing the program in Outlook you can use "olByCopy". Since you're writing it in Excel, it probably won't recognize "olByCopy" so you'll have to specify the literal "1" instead.

Changing the font etc has nothing to do with Excel or Outlook, but is just a matter of inserting the proper HTML tags such as <b> and </b> (for bold). The typeface (Arial, Times, whatever) is another HTML code; if you want that we can look up the right one.

 

[ushaanu]

If you have the email working, attaching a file to it is as easy as the To address and the subject line. Let's see, I have a program around here that adds attachments ... right, here it is.

oeml.Attachments.Add fnf,1 'olByValue (make copy)

In this statement, oeml is the email item; your variable for the same thing is msg. My variable fnf is the full file name, that is, the path and filename with extension. The second argument is the type of attachment; I'm not sure what variations there are, but judging by the comment I added, olByCopy must make a copy of the file and attach it to the email. Maybe another type of attachment sends the file itself, which then disappears from your hard drive, I don't know.

If you're writing the program in Outlook you can use "olByCopy". Since you're writing it in Excel, it probably won't recognize "olByCopy" so you'll have to specify the literal "1" instead.

Changing the font etc has nothing to do with Excel or Outlook, but is just a matter of inserting the proper HTML tags such as <b> and </b> (for bold). The typeface (Arial, Times, whatever) is another HTML code; if you want that we can look up the right one.

HI thanks for your Support, i need one changes in this code, i need instead of attachment send one common image send to all mailer.

 

[BobBridges]

Images are usually sent as attachments, too, in files using an extension of JPEG or GIF or whatever but it would still be a file attachment.

Or if you want the image to be included in the email, you probably need the HTML tag(s) for that, which I think would look something like this:

<img src="path/filename">

There are other arguments in the <IMG> tag that govern placement of the photo on the page, size, cropping, caption etc, but I think that's the correct syntax in the simplest form.

 

[ushaanu]

Images are usually sent as attachments, too, in files using an extension of JPEG or GIF or whatever but it would still be a file attachment.

Or if you want the image to be included in the email, you probably need the HTML tag(s) for that, which I think would look something like this:

<img src="path/filename">

There are other arguments in the <IMG> tag that govern placement of the photo on the page, size, cropping, caption etc, but I think that's the correct syntax in the simplest form.

HI Bob, thanks for your support, I added but still mail send with attachment not image.

i try but not find any solution. pls help if possible.

file attached.

TIA

 

[BobBridges]

I'll be away most of the morning, and anyway this is getting farther away from the topic of VBA/Excel. (Some folks here who get annoyed if you ask for help in any other subject.) I suggest you email me at robhbridges (@gmail) and we'll try to figure this out.

The first question I'll ask, though, is this: Is your email client using plain text, HTML, or RTF? They all behave differently.