E-mail by macro

E-mail by macro

Didn't find your answer?

Can anyone tell me if it is possible to send an excel sheet by the routing method of e-mail, using a macro. I want to send a form created by excel, to three people in turn who each need to modify the form, then return it to me. Can I set up a macro to do this? Also can I assign this macro to an object in the worksheet.
V Murphy

Replies (5)

Please login or register to join the discussion.

aw_logo_2019
By Accounting WEB
04th Jul 2001 21:35

Email from Excel

Try this macro which was sent to me sometime ago by

Julian Milano
IT Supervisor
Viviannes Collection
[email protected]

Sub EmailData()
Dim ol As Object, myItem As Object
Dim xlSheet1 As Object
Dim EmailAddr, ManagerAddr, Defectpath
EmailAddr = Range("Info!C9")
FilePath = "C:\XLFile.xls"
Set ol = CreateObject("outlook.application")
Set myItem = ol.CreateItem(olMailItem)
myItem.Subject = Range("Info!C10") & " " & Date
myItem.To = EmailAddr
myItem.Body = "Attachment Enclosed:" & Chr(13) & Chr(13)
myItem.Attachments.Add FilePath
myItem.NoAging = True
myItem.Send
Set ol = Nothing
End Sub

This only works with Microsoft Outlook. If you are using any other fringe packets then you have to modify it though can't guarantee it will work.

Also, the macro may not be alligned because accountingweb.com trims white spaces!

Jay Tanna

Thanks (0)
aw_logo_2019
By Accounting WEB
04th Jul 2001 21:49

This works on my Netscape Mail System

I have just written this using my Excel 97 and it it works. But like everything on this website, you are the judge and jury to decide the fait of this website. I think it is the best but who knows!

Sub SendIt()
Name = InputBox("Email Address", "Address")
Application.Dialogs(xlDialogSendMail).Show _
arg1:=Name, _
arg2:="Past Due Report"
End Sub

Hope this helps.

Jay Tanna

Thanks (0)
aw_logo_2019
By Accounting WEB
04th Jul 2001 19:37

To add to Andrew's suggestion
Tried this. Running macro got the send email dialog up ok bit I didn't get any names in the send box.

If you have the same trouble, you can add names and subject to the macro code. Edit the macro (tab/show code/modules/module1) and at the end of the line:-

Application.Dialogs(xlDialogSendMail).Show

Put a space and then (for example) :-

"[email protected],[email protected],[email protected]","Here's the sheet you wanted"

Thanks (0)
avatar
By EStansfeld
04th Jul 2001 13:48

E-mail by macro
Yes, it's possible.

Unfortunately the macro key-stroke recorder doesn't really help much, so you need to write some code to start Outlook and create an email.

Assigning the macro to an object in the worksheet is straightforward.

Edward Stansfeld
[email protected]

Thanks (0)
avatar
By arandall
04th Jul 2001 14:08

Macro recorder will do the trick!
Assuming that you are using Excel version 97 or better, you can set up a macro for the worksheet that you want to route using the macro recorder. Set up the worksheet, then use the macro recorder to record the next instructions:

File Menu
Send to
Routing recipient

Enter the details of the person you want to e-mail the worksheet to

Click OK to all the boxes

Click STOP on the macro recorder

Set up a button on another worksheet and assign the macro.

Every time you click, it will route the worksheet via the e-mail.

Job done!

Good Luck

Thanks (0)