Excel Email Macro

[tdpman]

Ok, you brainiacs, is there a macro that can be written that will make Excel automatically email the current spreadsheet or workbook to someone?

I have a spreadsheet that I'm running various macros on and in the middle of this process, I have to email it to certain individuals before I make any further changes to it. Once it's emailed to them, I go in and delete the duplicates from it. I already have a macro that will delete the dups, so all I need is the email macro. I would like to have a macro that will automatically email it to them, so that I don't have to stop in the middle of my work and send it. Hope this makes sense. Any ideas?

 

[hopelessliar]

which email client?

 

[SkipVought]

Hi,

Specifically it is the Send Mail Dialog
xlDialogSendMail recipients, subject, return_receipt
in the form...

Application.Dialogs(xlDialogSendMail).Show

Check out VBE Help...
Dialogs Collection Object
See Also Properties Methods Events Specifics
Application
Dialogs (Dialog)

A collection of all the Dialog objects in Microsoft Excel. Each Dialog object represents a built-in dialog box. You cannot create a new built-in dialog box or add one to the collection. The only useful thing you can do with a Dialog object is use it with the Show method to display the dialog corresponding dialog box.

Using the Dialogs Collection
Use the Dialogs property to return the Dialogs collection. The following example displays the number of available built-in Microsoft Excel dialog boxes.

MsgBox Application.Dialogs.Count
Use Dialogs(index), where index is a built-in constant identifying the dialog box, to return a single Dialog object. The following example runs the built-in File Open dialog box.

dlgAnswer = Application.Dialogs(xlDialogOpen).Show
The Microsoft Excel Visual Basic object library includes built-in constants for many of the built-in dialog boxes. Each constant is formed from the prefix "xlDialog" followed by the name of the dialog box. For example, the Apply Names dialog box constant is xlDialogApplyNames, and the Find File dialog box constant is xlDialogFindFile. These constants are members of the XlBuiltinDialog enumerated type. For more information about the available constants, see Built-in Dialog Box Argument Lists. Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[tdpman]

By email client do you mean like Outlook 98?

 

[hopelessliar]

exactly so. If it's outlook there is a lot of info available already. If it's notes, the info is a bit more scarce

 

[tdpman]

well, I'm not finding what I need or I'm looking in the wrong place or something

 

[xlbo]

Well I got 31 hits searchin VBA and office forums for "email excel" in the subject line and loads more searching in the "full text" Rgds
Geoff
"Some cause happiness wherever they go; others whenever they go."
-Oscar Wilde

 

[tdpman]

well thanks anyway, i guess

 

[tdpman]

OK, so I figured out the email part with this code:

Worksheets(Array("sheet2", "sheet5&quot).Copy
ActiveWorkbook.SendMail "email@hotmail.com", "test"

But what do I need to add to the end of this code to close the "copy" that is created and emailed and return me to my original worksheet?

Thanks!

 

[Gavona]

Right, so you have created a separate workbook containing just some of the worksheets from the book you are working on (though I am suprised your first line achieves this). You have emailed it using SendMail. It is still the active workbook so:
ActiveWorkbook.Close
This should by default return you to the workbook you were working on but you may wantto ensure this with.
xxx.Activate
I have recently been creating a routine to split a workbook into bits each to be emailed to different recipients. I have added error trapping to capture when the SendMail fails (e.g. due to unrecognised email address or to me having exceeded my outlook storage limit).

Sub ReturnByEmail()
Dim Msg, Style, Title, Help, Ctxt, Response, ReturnAddress       'define variables

ReturnAddress = "xxx@buckscc.gov.uk"

Msg = "Send xxxxx"
Style = vbYesNo + vbCritical + vbDefaultButton2    ' Define buttons.
Title = "Monthly Monitoring Return" + " " + ThisWorkbook.Name   ' Define title.
Response = MsgBox(Msg, Style, Title)

If Response = vbYes Then    ' User chose Yes - email will be sent.

        On Error GoTo ErrorHandler         'need to trap invalid email addresses or other mail failure
        ActiveWorkbook.SendMail Recipients:=ReturnAddress, Subject:=Title, RETURNRECEIPT:=True
        On Error GoTo 0
        
        Msg = "Your email has been sent - and should appear in Outlook, Sent Items"
        Style = vbOK + vbDefaultButton1    ' Define buttons.
        Title = "Monthly Monitoring Return" + " " + ThisWorkbook.Name   ' Define title.
        Response = MsgBox(Msg, Style, Title)
Else    ' User chose No.
    
End If
Exit Sub
    
ErrorHandler:
      Msg = "Mail failure -  is your mailbox too full?   Try sending an email directly from outlook to " + ReturnAddress + " before reporting the fault"
      Style = vbOK + vbDefaultButton1    ' Define buttons.
      Title = "Monthly Monitoring Return" + " " + ThisWorkbook.Name   ' Define title.
      Response = MsgBox(Msg, Style, Title)

End Sub