Help with emailing attachments (VBA novice!) (1 Viewer)

[Big Pat]

Hi,

Using Access/Excel/outlook 2003 for the following

I have approx 30 Excel workbooks to send to 30 different email addresses, one each. I'm a complete novice at this stuff and have read lots of posts in lots of places without fully understanding it.


The closest I have come is the following code, which assumes you want to send the ACTIVE document. I don't want to to do that, so I have amended it a bit. A bit too much probably, because although it does send the email, it does NOT send the attachment. No error message or anything, just no attachment.

Private Sub cmdEmailAttachments_Click()
'Adapted from Ron de Bruin's code at... http://msdn.microsoft.com/en-us/library/ff458119(office.11).aspx#odc_office_UseExcelObjectModeltoSendMail_MailingWorkbookasEmailAttachment

' Works in Excel 2000, Excel 2002, Excel 2003, Excel 2007, Excel 2010, Outlook 2000, Outlook 2002, Outlook 2003, Outlook 2007, Outlook 2010.
' This example sends the last saved version of the Activeworkbook object .
    Dim OutApp As Object
    Dim OutMail As Object

    Set OutApp = CreateObject("Outlook.Application")
    Set OutMail = OutApp.CreateItem(0)

    On Error Resume Next
   ' Change the mail address and subject in the macro before you run it.
    With OutMail
        .To = "someone@someplace.com"
        .CC = ""
        .BCC = ""
        .Subject = "Enter subject line here"
        .Body = "Enter message here"
        '.Attachments.Add ActiveWorkbook.FullName    -  I don't want to open the document first so I have commented out this line
        ' You can add other files by uncommenting the following line.  -  This is what I want to do instead
        Attachments.Add ("\\UHSHARES1\WMS_CLRN\CLRN\Accruals\Reports\Specialty Leads\Email Test workbook.xls")   ' I copied the full path of the workbook from the Web toolbar in Excel
        ' In place of the following statement, you can use ".Display" to
        ' display the mail.
        .Send
    End With
    On Error GoTo 0

    Set OutMail = Nothing
    Set OutApp = Nothing


End Sub

Is there a simple change I can make to this to get it to send the attachment? How much of the code would I need to repeat for the other 29 emails? The section between 'With OutMail' and 'End With', or other parts too?

I realise it would probably make sense to have the email addresses and filenames in a table and have the code read from that, but I think I'd like to try it in this format first, just to help me get my head round it.

Thanks

 

[Big Pat]

D'oh! I just spotted a missing dot at the start of the Attchment line in the code. I'd assumed it was a problem in the way I was syntaxing (is that a word?) the filename, but it was far simpler error!

This now works and I'm off to experiment with adding the other 29 filenames. I'd still be really grateful for any pointers.

Thanks,

Pat.