E-Mail Code to send Worksheet (or book) & editable messa

Mister H

Well-known Member
Joined
Mar 6, 2002
Messages
1,507
Hi All:

I was wondering if anyone can tell me if they know of a working code that will send a Worksheet or Workbook as an attachment in Outlook and ALSO allow the user to edit the body of the e-mail prior to sending?

I have found coding on Ron deBruin's (sorry if the spelling is incorrect)website which gets me close but I can't seem to find a code that will allow the user to type their own message prior to sending the e-mail. There must be a way of doing this.

Can anyone assist???

THANKS,
Mark
Sudbury, Ontario
 

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.

Jon von der Heyden

MrExcel MVP, Moderator
Joined
Apr 6, 2004
Messages
10,810
Office Version
  1. 365
Platform
  1. Windows
Hi Mark,

Have you considered linking the message to a cell?

E.g. Body = Range("A1")

Or linking it to Application.InputBox....

Regards,
Jon

:)
 

Mister H

Well-known Member
Joined
Mar 6, 2002
Messages
1,507
Hi Jon:

How would I go about those options? I understand the part about having a box to type your message in but I am not sure what you are talking about in your other suggestion. SORRY it is probably my VBA ignorance talking...
The code I will use probably use is:


Code:
Sub Mail_workbook_Outlook()
'This example send the last saved version of the Activeworkbook
'You must add a reference to the Microsoft outlook Library
    Dim OutApp As Outlook.Application
    Dim OutMail As Outlook.MailItem
    Set OutApp = CreateObject("Outlook.Application")
    Set OutMail = OutApp.CreateItem(olMailItem)
    With OutMail
        .To = "ron@debruin.nl"
        .CC = ""
        .BCC = ""
        .Subject = "This is the Subject line"
        .Body = "Hi there"
        .Attachments.Add ActiveWorkbook.FullName
        'You can add other files also like this
        '.Attachments.Add ("C:\test.txt")
        .Send   'or use .Display
    End With
    Set OutMail = Nothing
    Set OutApp = Nothing
End Sub

How would I alter this for the A1 thing.

THANKS,
Mark
 

Jon von der Heyden

MrExcel MVP, Moderator
Joined
Apr 6, 2004
Messages
10,810
Office Version
  1. 365
Platform
  1. Windows
Well it really depends on how you want the process to work. You could either create a section in the spreadsheet to define who you want to send it to, change the subject to the name of the file, and include a message.

So for example A1 could host: jon_vonderheyden@oohay.oc.ku
A2 could host: "Dear Jon, please review the attached and return completed list to me as soon as possible. Thank you.

Then the code could look something like:

Code:
To = Range("A1").Value 
        .CC = "" 
        .BCC = "" 
        .Subject = ActiveWorkbook.Name 
        .Body = Range("A2").Value

or to use an input box for the subject change it to

Code:
.Body = Application.InputBox(prompt:="Message", Type:=2)

Think that should work :unsure:
 

Mister H

Well-known Member
Joined
Mar 6, 2002
Messages
1,507
Hi Jon:

I like the Message Box idea. However I noticed that the user is limited as to the amount of characters they can enter. Is this adjustable or is that the way Excel is? If the length can not be adjusted I will try out your other method. THANKS fro all your help...

Bye 4 Now,
Mark

P.S. Do you know if the code I have in the above posting could be altered to asked the user if they want to add a message to the e-mIl and if so type it in A1? Just curious.
 

Forum statistics

Threads
1,141,587
Messages
5,707,252
Members
421,498
Latest member
matinebi

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top