Macro to transfer Selected Range to Outlook

**JuneTwentyFourteen** · 06-19-2014, 06:35 AM

Good Morning

I hope someone may be able to help.

I am currently using a macro courtesy of www.rondebruin.nl to embed selected cells into Outlook. The macro commands do everything I want, except it prompts an e-mail to be sent automatically. Does anyone know of a way of displaying the e-mail before it gets sent. I have tried replacing "send" with "display" but this doesn't seem to work.

I have pasted the macro below, any suggestions would be great.

Regards
Colin

Please Login or Register  to view this content.

**diberlee** · 06-19-2014, 06:48 AM

It should be as you've tried... Changing .send to .display. You definitely included the "." when you tried changing to .display?

**diberlee** · 06-19-2014, 06:55 AM

Actually, I misunderstood. I thought it was just emailing a sheet. I commented out .send and the ActiveWorkbook.EnvelopeVisible = False line, and running the macro now shows the envelope, populates the fields with what you specify and stops there.

Is that what you wanted?

**JuneTwentyFourteen** · 06-19-2014, 07:22 AM

Thanks for that, getting closer to what I am trying to achieve. I notice that the draft e-mail appears within the Excel Worksheet rather than Outlook.

Ultimately, what I would like is to run a macro that populates a draft e-mail in outlook with a selected range of cells, within the body of the e-mail as opposed to being an attached worksheet. The user can then review the e-mail before it goes out.

Regards

Colin

**diberlee** · 06-19-2014, 09:25 AM

Ah, I see. that's what I originally thought your code was meant to do.

I adapted another of Ron De Bruin's samples for that but I can't seem to find it on the site any more. Here is my code with anything specific to what I use it for taken out. Write back here if you need any help adapting it to your needs. All credit to Ron

Please Login or Register  to view this content.

Please Login or Register  to view this content.

**JuneTwentyFourteen** · 06-19-2014, 09:39 AM

Originally Posted by diberlee

Ah, I see. that's what I originally thought your code was meant to do.

I adapted another of Ron De Bruin's samples for that but I can't seem to find it on the site any more. Here is my code with anything specific to what I use it for taken out. Write back here if you need any help adapting it to your needs. All credit to Ron

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Thanks, I have used the macro at the top, which does look as though it should work, my outlook draft opens up but appears blanks. I assume I have to set the range, am maybe going wrong somewhere with the HTML Range. The range on my workbook is Sheet1!B1:J45, how/where do I paste this in?

Colin

**diberlee** · 06-19-2014, 09:42 AM

Hi,

You should set that in the 8th line down of the first macro.

Cheers

EDIT: Updated earlier post to show correct code for the sheet/range you are using

**JuneTwentyFourteen** · 06-19-2014, 09:59 AM

Thanks for your continued help. I am now receiving an error message, "Compile Error, Sub or function not defined". I am not sure quite where I am going wrong!

The macro now looks like this in VBA:

Please Login or Register  to view this content.

**diberlee** · 06-19-2014, 10:57 AM

Hi,

Sorry for slow reply. Busy day today

I checked and it's working fine for me. Recopy the code from my 3rd message (I updated it to work with the sheet/range you need). Make sure you take both the sub and the function. Paste them in the same module for simplicity.

Cheers

**JuneTwentyFourteen** · 06-19-2014, 10:59 AM

Diberlee,

I have realised what I have been doing wrong, the macro has to be run with the RangetoHTML subroutine. (Next time I'll pay closer attention.) I have now put the two macros together and have achieved what I wanted.

Many thanks indeed for your help!

Regards

Colin

http://www.rondebruin.nl/win/s1/outlook/bmail2.htm

Please Login or Register  to view this content.

**JuneTwentyFourteen** · 06-19-2014, 11:00 AM

Originally Posted by diberlee

Hi,

You should set that in the 8th line down of the first macro.

Cheers

EDIT: Updated earlier post to show correct code for the sheet/range you are using

Go it!! Thanks very much for your help, I am most grateful,

Regards

Colin

**bmbalamurali** · 06-24-2014, 05:42 AM

Hi Colin/Diberlee,

your post helped me a lot and i used the similar thing in my work.

Kindly let me know how to insert signature in every mail that is generated by this macro.

I tried adding signature code by Ron... but am failed, i didn't know where to add. Kindly help me in this.

Thanks for your time and consideration.

-Bala

Macro to transfer Selected Range to Outlook

LinkBack

Thread Tools

Rate This Thread

Display

Macro to transfer Selected Range to Outlook

Re: Macro to transfer Selected Range to Outlook

Re: Macro to transfer Selected Range to Outlook

Re: Macro to transfer Selected Range to Outlook

Re: Macro to transfer Selected Range to Outlook

Re: Macro to transfer Selected Range to Outlook

Re: Macro to transfer Selected Range to Outlook

Re: Macro to transfer Selected Range to Outlook

Re: Macro to transfer Selected Range to Outlook

Re: Macro to transfer Selected Range to Outlook

Re: Macro to transfer Selected Range to Outlook

Re: Macro to transfer Selected Range to Outlook

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] transfer a selected range of data on one sheet to another workbook

send selected range in email with default outlook email signature included

[SOLVED] How to set range within a macro based on what range is selected on the worksheet

when a certain category is selected

[SOLVED] Is there a macro to transfer a row of data from top to bottom row of range ??

Bookmarks

Bookmarks

Posting Permissions