Email with VBA

[VbaHell]

Good Evening Excel world, I am trying to run a VBA macro via a button to send multiple emails to suppliers in column A with a text line " Please confirm the delivery date" but have no idea how to do this.

Column A would have the supplier name and column B to J would have the information to send.

Can anybody help please

 

[oCoCarbon]

There are three ways given on this site http://www.databison.com/index.php/3-nifty-ways-to-send-email-using-excel/

The first one seems simple enough, so long as you don't mind confirming each email. Otherwise there are a couple of other ways suggested.

 

[VbaHell]

Thank you for your help on this

 

[Michael M]

Have a look here.
This is the best site for this type of advice.

http://www.rondebruin.nl/sendmail.htm

 

[VbaHell]

This is great, Thank you

 

[Michael M]

Also have a look here at one of my previous Email posts
Particularly Post #4

http://www.mrexcel.com/forum/showthread.php?t=525743

 

[VbaHell]

Thanks again Michael

 

[mark hansen]

I hope you don't mind me resurfacing this thread, but I think it applies.

I've been trying to get a button to e-mail a PDF attachment of a pre-determined range. I used the code at http://www.rondebruin.nl/sendmail.htm and its amazing. I don't understand it much, but it works.

The problem I'm having is it stops to ask for the name of the PDF document. I took the modules from the accompanying spreadsheet (at the website) and Put in the Create PDF module, Create PDF mail module and the Functions Module and set it to a button. Like I said ,it works.

I found the section that has...

'Call the function with the correct arguments
FileName = RDB_Create_PDF(ActiveWorkbook, "", True, True)

'For a fixed file name and overwrite it each time you run the macro use
'RDB_Create_PDF(ActiveWorkbook, "C:\Users\Ron\Test\YourPdfFile.pdf", True, True)

and that's what I wanted to do so I added my path between the quotes.

The problem is it still asks for the file name of the resulting PDF.

When I used F8 to step through the code, it appears to not even hit those lines. (That line appear in a few places.)

If I could go from hitting the button to seeing the e-mail without any other action that would be great.

Other things I want to do is modify the code so its not to hard code with the path. Saving to My documents and over writing is OK, but I need it to work for different users, so it needs to key off of the user name.

Additionally, I would like the e-mail "To" block to pick up an e-mail address from a certain cell.

But as I haven't gotten to not needing the PDF file name yet, I haven't moved on to the dynamic path or dynamic "To" yet.

Any thoughts?

Another related question is, can I just use code under the button? When things start jumping from module to module, I get a little lost. Is there a way to have the code (in the button Private sub) Create the PDF from the range, then attach that to an e-mail message, put in "To" from a cell, put in a subject and canned text and wait for me to click send.

Is there some inherent to sending mail that it needs to be in a module? Or is it because its probable different routines may want that e-mail code and its best to write the code one time and use it many times? All my searches use code in the modules (I was hoping to get it a little simpler).

Thanks for any insight,
Mark

 

[Michael M]

Hi Mark
Domski helped me out with this code for saving the file to PDF and it doesn't ask for a file name.
It simply sends it to G:temp and uses the name of the workbook as the filename.

Sub NuCreate_PDF()
'Dom Hill, June 2008
'This code has only been tested with Adobe Acrobat 6.0 Professional
'For this code to work the Adobe Distiller VBA library must be enabled
'Select Tools...References in the VBA editor and check it from the list
 'MsgBox "Under Construction, Try Again Later"
 'Exit Sub
Dim tempPDFFileName As String, ws As Worksheet
Dim tempPSFileName As String
Dim tempPDFRawFileName As String
Dim tempLogFileName As String
Dim mypdfDist As New PdfDistiller
For Each ws In ThisWorkbook.Worksheets
    tempPDFRawFileName = "G:\Temp\" & ActiveWorkbook.Name
    tempPSFileName = tempPDFRawFileName & ".ps"
    tempPDFFileName = tempPDFRawFileName & ".pdf"
    tempLogFileName = tempPDFRawFileName & ".log"
 
'Prints workbook 'Output' as a pdf, an array of sheets can be printed if required
 
    ActiveWorkbook.PrintOut Copies:=1, preview:=False, ActivePrinter:="Adobe PDF", _
        printtofile:=True, Collate:=True, prtofilename:=tempPSFileName
    mypdfDist.FileToPDF tempPSFileName, tempPDFFileName, ""
 
    Kill tempPSFileName
    Kill tempLogFileName
Next ws
Set mypdfDist = Nothing
End Sub

To resolve the picking up a name list for the "To" problem, I use this.
It will allow me to send to up to 100 recipients

'************Start of emailing code*********

         For i = 7 To 100 'SELECTS NAMES FROM RAGE I7 TO I100
    If Sheets("YOUR SHEET NAME HERE").Range("I7").Value <> "" Then 'Change Sheet name to suit
        nameList = nameList & ";" & Sheets("YOUR SHEET NAME HERE").Range("I" & i).Value 'Change Sheet name to suit
    End If
Next
    Set Mail_Object = CreateObject("Outlook.Application")
        With Mail_Object.CreateItem(o)
            .Subject = "This is the subject"
            .To = nameList
            .Body = "This is the text in the body" 'Change comments to suit
            .Attachments.Add tempPDFFileName
            .Send
    End With
        MsgBox "E-mail successfully sent", 64
        Application.DisplayAlerts = False
Set Mail_Object = Nothing
End Sub

That might help solve a couple of problems...post back if you need more assistance.

 

[mark hansen]

Thanks Michael. Very helpful!!