Send Excel file by email, but Not to Save

Hi Folks

I need to prepare an Excel file, send it by email, but not to save it, using a Macro. What must be entered into the subject field is found in a cell in the file. In this case, Cell E2. I've attached an example. No recipients to worry about.

Many thanks

Patish

Start here:

http://www.rondebruin.nl/win/section1.htm

Hi Alan

Thanks for your quick reply and the link. Maybe I missed it, but the code in the examples deals mainly with addresses and bodies of emails. What I need is to enter the info in the cell E2 into the Subject field of the open email.

Thanks

Patish

In Ron's example below:

Please Login or Register  to view this content.

Replace this line of code:

Please Login or Register  to view this content.

with

Please Login or Register  to view this content.

Whichever code you use, just change up the Subject line to reflect the range containing the subject

Hi Alan

Once again, many thanks.

I have attached the file with the adapted Macro. I have deleted the recipient's address and have changed the "Send" to "Display" as I just need only to display the email and enter in the recipients' addresses manually.

Here is the issue: The file that I want to send is actually a Worksheet that I have copied as a new Book. I want to send it immediately without saving it as a separate file. I tried to send the worksheet. The email is displayed, but the file is not attached. I reckon it does not attach because it is not a file yet??? Are you able to change the code accordingly?

Another point: I will be placing a "Button" on the worksheet to activate the Macro. Where do I place the code in the macro to the delete the button before send the file?

Apologies for the additional stuff.

Much appreciated

Patish

as mentioned already

extract worksheet

create the "temp" file
while creating the Temp file you can convert it to xlsx which will get rid of the button
kill it after your are finished

http://www.rondebruin.nl/win/s1/div/mail2.htm

As per suggested.

Please Login or Register  to view this content.

@humdingaling

buttons get copied even as saved xlsx.
See top of code to avoid that issue.

Hi Bakerman2

Many thanks for your input. Just a small question: If I want to add in multiple email addresses how would the .to ="" be adapted? Above Alan had only one address and it's OK. However, I placed the ";" between the two addresses and it did not work. How do I add multiple addresses in the macro? As an alternative how do I go about adapting the macro to link to a list addresses on a spreadsheet in the same workbook? If I wish to add and delete addresses without changing the Macro each time.

Tx

Patish

I placed the ";" between the two addresses and it did not work

you must not be doing the syntax correctly

anyway you can set a a range on your spreadsheet with email addresses
then just loop that range and create the .to

Please Login or Register  to view this content.

then make

Please Login or Register  to view this content.

Hi Humdingaling

I am using Alan's code above. So I have replace the email address of Ron with " = sTo". The Range I have given a name. Now, where do I place your code?

Tx

Patish

Like this.

Please Login or Register  to view this content.

Thank you Bakerman. Thank you Alan. Thank you everyone for your input. The Macro works like a charm.

Once again thank you.

You're very welcome and thanks for adding rep points.

If this answers your question please mark the thread as SOLVED. Thanks