PDA

View Full Version : Solved: Auto Email Information?



Rlb53
03-02-2012, 01:49 PM
Good Day Everyone !

I will be generating Information in a specific range repetetively.

When this information is updated, there is a specific group of individuals that I need to email the changed data to.

Is there a way to setup an auto Copy/Paste Function with VBA into an Outlook Email?

Let's just say the Range I want to share is Sheets("sheet5").Range("D1:F10).Copy

Thanks

TMShucks
03-03-2012, 02:21 AM
Have a look at Ron de Bruin's web pages (http://www.rondebruin.nl/tips.htm) for everything you'd ever want to know about emailing with Excel VBA.

Regards

Rlb53
03-03-2012, 08:23 AM
Perfect ! Thank You !

mdmackillop
03-03-2012, 08:25 AM
Can you post your final code for the benefit of others?

Rlb53
03-03-2012, 09:33 PM
I actually used something a little different, but functional for my purpose.
The code used selects and copies the designated range to be shared via Email.

It does require some user interaction... but minimal.. three clicks and the information is on it's way.

I did find though... that if I did not go back and insert the "Application.CutCopyMode=False" command at the beginning of my macros....
I would end up with the data copied into a worksheet as the process continued.

The code may be crude... but it performs the task I require for the application.

Thanks


Private Sub CommandButton7_Click()

Application.CutCopyMode = False

'Instructions to the User in MsgBox
MsgBox "You must ""Right Click"" and ""Paste"" Into the Email Box When It Appears."

Sheets("sheet3").Select

'Data Exchange Below to Designated Cell
Sheets("sheet3").Range("h3").Value = Sheets("sheet2").Range("d3").Value

' Range required to transmit in Email
Sheets("Sheet3").Range("f2:k5").Copy

'Cell with Hyperlink Information requesting Outlook Email to Open already
'populated with Email Group Information and Subject Line. Both will be
'consistent. (mailto:EmailGroup?subject=Repetetive%20Subject)
Sheets("sheet3").range("F7").select
'Email Hyperlink Activated
Selection.Hyperlinks(1).Follow NewWindow:=False, AddHistory:=True


End Sub