Email report directly from within the workbook.

[ABabeNChrist]

I am trying to come up with a way that once a report has been completed the user could then email report directly from within the workbook.
Here is what I was thinking
I would add an additional worksheet that is linked to the client’s info, like name and email address
Name in A1
Email in A2
I would then use A3 for the Subject line of the email and A4 for the body
That way the email can be changed or modified when ever need or just leave as a template.
Then by selecting a send button I would have the option of attaching a file (report) to email before sending, then send.
Here’s what I have so far

Code: Select all

    Dim OutApp As Object
    Dim OutMail As Object

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

    On Error Resume Next
    With OutMail
        .To = Sheets("Sheet1").Range("A2")    'email address
        .BCC = ""
        .Subject = Sheets("Sheet1").Range("A3")
        .Body = Sheets("Sheet1").Range("A4")
        'Attach documents here
        .Attachments.Add ("C:\Documents and Settings\UserName\My Documents\report.pdf")
        .Display    'or use .Display or .Send
    End With
    On Error GoTo 0

    Set OutMail = Nothing
    Set OutApp = Nothing

[HansV]

So what's your question?

[ABabeNChrist]

Hi Hans
I figured out the email address, Subject and Body line, but not sure how to attached different reports that have different names using possibly a dialog to search for file folder. Once folder has been attached then email will be sent.

[HansV]

Add the following declarations at the beginning of the code:

Code: Select all

    Dim i As Long
    Dim fd As FileDialog

Replace the line

Code: Select all

        .Attachments.Add ("C:\Documents and Settings\UserName\My Documents\report.pdf")

with

Code: Select all

        Set fd = Application.FileDialog(msoFileDialogOpen)
        fd.AllowMultiSelect = True
        If fd.Show Then
            For i = 1 To fd.SelectedItems.Count
                .Attachments.Add fd.SelectedItems(i)
            Next i
        End If
        Set fd = Nothing

[ABabeNChrist]

That works fantastic Hans, Thank you
I now have another little twist to this
Since this code is only set up for Outlook
What if user, uses Yahoo or Google as their primary email server

[Goshute]

Their mail service provider doesn't matter, but they'll need to use Outlook as their mail client. My home email service provider is GMail, but I use Outlook. If they use webmail, I'm not sure there is anything you can do for them.

[HansV]

Bad luck for them. You can use the SendMail method of the workbook to send a copy of the workbook through the user's default mail client, but you cannot attach other files programmatically. That can only be done with Outlook.

[ABabeNChrist]

Thank you Hans and Goshute for your assistance
I seem to be having a little problem; I’m unable to send email using Microsoft Outlook 2007.
I’m not familiar with outlook at all. When I use line of code used in this thread .Send
It appears that it sends, but in reality it does not, so I reset the code to .Display to see what might be going on and I noticed that there was no send button. Do I need to register outlook or ?

[HansV]

It works for me with Excel 2007 and Outlook 2007 (SP2). Have you looked in the Sent Items folder?

[ABabeNChrist]

Hi Hans
This is how it appears when I run code with .Display

Outlook.JPG

[HansV]

Is Outlook the default e-mail program on your computer?

[ABabeNChrist]

no

[HansV]

See if it makes a difference of you do so, even if only temporarily.

[ABabeNChrist]

I first set to default, no change, I then looked around and went to Tools\account settings\Updated with new email address and added a new email address, It seems when I was setting up a new email address, it would run through the process and then show this dialog, I would then retype password select OK, this same dialog would then close and reopen. I know the password is correct.

Outlook email.JPG

I did notice though that I seem to have a Send button now, but does not seem to send. It clicks and appears to send does not.

[HansV]

Try using a proper e-mail address instead of a Yahoo address.

[ABabeNChrist]

I'm not sure I understand what you mean

[HansV]

I apologize, I let my dislike of everything associated with Yahoo take over.

It appears that you have problems setting up your Yahoo account in Outlook. Do you have another e-mail account that you could use instead of the Yahoo account?

[ABabeNChrist]

I tried my hotmail and Gmail no such luck
I think I'm going to re-start my computer then try again, its worth a shoot......

[HansV]

That is not a bad idea anyway, but doesn't your ISP give you a "normal" e-mail account (I mean, not a webmail account such as Yahoo, Gmail and Hotmail)?

[Goshute]

.Display does not also perform the equivalent of .Send. When you send a test message with the same size and number of attachments manually, how long does it take to send? If your Internet service is not fast, the message may sit in the Outbox for a while before it's completely sent.