|
Pasting from Excel to Outlook with VBA code
Hi,
I am trying to Copy the contents of an Excel worksheet into an Outlook mail item, like a simple copy and paste. Is there any code that can do this, I have examples that can populate from the worksheet but I need it to be in a table format. Can anyone help?? |
Pasting from Excel to Outlook with VBA code
Hi spences10
Try this http://www.rondebruin.nl/mail/folder3/mail2.htm -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "spences10" wrote in message ups.com... Hi, I am trying to Copy the contents of an Excel worksheet into an Outlook mail item, like a simple copy and paste. Is there any code that can do this, I have examples that can populate from the worksheet but I need it to be in a table format. Can anyone help?? |
Pasting from Excel to Outlook with VBA code
Hi Ron,
This is fantastic!! thank you very much. There is one thing however, I need to be able to add some text at the top of the e-mail. Oh and is it possible to add an auto signature to the mail as well? Ron de Bruin wrote: Hi spences10 Try this http://www.rondebruin.nl/mail/folder3/mail2.htm -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "spences10" wrote in message ups.com... Hi, I am trying to Copy the contents of an Excel worksheet into an Outlook mail item, like a simple copy and paste. Is there any code that can do this, I have examples that can populate from the worksheet but I need it to be in a table format. Can anyone help?? |
Pasting from Excel to Outlook with VBA code
Check out this first
http://www.rondebruin.nl/mail/folder3/signature.htm Must go now but get back to you this evening Let me know if the second example is working for you -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "spences10" wrote in message ups.com... Hi Ron, This is fantastic!! thank you very much. There is one thing however, I need to be able to add some text at the top of the e-mail. Oh and is it possible to add an auto signature to the mail as well? Ron de Bruin wrote: Hi spences10 Try this http://www.rondebruin.nl/mail/folder3/mail2.htm -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "spences10" wrote in message ups.com... Hi, I am trying to Copy the contents of an Excel worksheet into an Outlook mail item, like a simple copy and paste. Is there any code that can do this, I have examples that can populate from the worksheet but I need it to be in a table format. Can anyone help?? |
Pasting from Excel to Outlook with VBA code
Hi Ron,
Thanks again for your help, I am struggling at the moment though. Using the code supplied by yourself I have managed to paste from the workbook into an outlook mail item, but adding a text string is proving difficult as I can only have either the pasted item or the text string but not both. = ( Sub MailDiscretionRequest() ' Don't forget to copy the function RangetoHTML in the module. ' Working in Office 2000-2007 Dim Rng As Range Dim OutApp As Object Dim OutMail As Object Dim StrBody As String With Application .EnableEvents = False .ScreenUpdating = False End With Set Rng = Nothing 'Set Rng = ActiveSheet.UsedRange 'You can also use a sheet name Set Rng = Sheets("DiscretionRequest").Range("DiscReq") Set OutApp = CreateObject("Outlook.Application") OutApp.Session.Logon Set OutMail = OutApp.CreateItem(0) StrBody = "Hi Colin, discretion request for your attention." & vbNewLine & vbNewLine On Error Resume Next With OutMail .To = "Dolder, Colin : Business Banking Risk" .CC = "" .BCC = "" .Subject = "DISCRETION REQUEST" .HTMLBody = RangetoHTML(Rng) '.Body = StrBody '.Send .Display End With On Error GoTo 0 With Application .EnableEvents = True .ScreenUpdating = True End With Set OutMail = Nothing Set OutApp = Nothing End Sub thanks, Scott Spence Ron de Bruin wrote: Check out this first http://www.rondebruin.nl/mail/folder3/signature.htm Must go now but get back to you this evening Let me know if the second example is working for you -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "spences10" wrote in message ups.com... Hi Ron, This is fantastic!! thank you very much. There is one thing however, I need to be able to add some text at the top of the e-mail. Oh and is it possible to add an auto signature to the mail as well? Ron de Bruin wrote: Hi spences10 Try this http://www.rondebruin.nl/mail/folder3/mail2.htm -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "spences10" wrote in message ups.com... Hi, I am trying to Copy the contents of an Excel worksheet into an Outlook mail item, like a simple copy and paste. Is there any code that can do this, I have examples that can populate from the worksheet but I need it to be in a table format. Can anyone help?? |
Pasting from Excel to Outlook with VBA code
Hi Scott
Use this line StrBody = "Hi Colin, discretion request for your attention." & "<br<br" And this .HTMLBody = StrBody & RangetoHTML(rng) -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "spences10" wrote in message oups.com... Hi Ron, Thanks again for your help, I am struggling at the moment though. Using the code supplied by yourself I have managed to paste from the workbook into an outlook mail item, but adding a text string is proving difficult as I can only have either the pasted item or the text string but not both. = ( Sub MailDiscretionRequest() ' Don't forget to copy the function RangetoHTML in the module. ' Working in Office 2000-2007 Dim Rng As Range Dim OutApp As Object Dim OutMail As Object Dim StrBody As String With Application .EnableEvents = False .ScreenUpdating = False End With Set Rng = Nothing 'Set Rng = ActiveSheet.UsedRange 'You can also use a sheet name Set Rng = Sheets("DiscretionRequest").Range("DiscReq") Set OutApp = CreateObject("Outlook.Application") OutApp.Session.Logon Set OutMail = OutApp.CreateItem(0) StrBody = "Hi Colin, discretion request for your attention." & vbNewLine & vbNewLine On Error Resume Next With OutMail .To = "Dolder, Colin : Business Banking Risk" .CC = "" .BCC = "" .Subject = "DISCRETION REQUEST" .HTMLBody = RangetoHTML(Rng) '.Body = StrBody '.Send .Display End With On Error GoTo 0 With Application .EnableEvents = True .ScreenUpdating = True End With Set OutMail = Nothing Set OutApp = Nothing End Sub thanks, Scott Spence Ron de Bruin wrote: Check out this first http://www.rondebruin.nl/mail/folder3/signature.htm Must go now but get back to you this evening Let me know if the second example is working for you -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "spences10" wrote in message ups.com... Hi Ron, This is fantastic!! thank you very much. There is one thing however, I need to be able to add some text at the top of the e-mail. Oh and is it possible to add an auto signature to the mail as well? Ron de Bruin wrote: Hi spences10 Try this http://www.rondebruin.nl/mail/folder3/mail2.htm -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "spences10" wrote in message ups.com... Hi, I am trying to Copy the contents of an Excel worksheet into an Outlook mail item, like a simple copy and paste. Is there any code that can do this, I have examples that can populate from the worksheet but I need it to be in a table format. Can anyone help?? |
Pasting from Excel to Outlook with VBA code
Hi Ron,
I have already tried this and no joy. Confused = ( Ron de Bruin wrote: Hi Scott Use this line StrBody = "Hi Colin, discretion request for your attention." & "<br<br" And this .HTMLBody = StrBody & RangetoHTML(rng) -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "spences10" wrote in message oups.com... Hi Ron, Thanks again for your help, I am struggling at the moment though. Using the code supplied by yourself I have managed to paste from the workbook into an outlook mail item, but adding a text string is proving difficult as I can only have either the pasted item or the text string but not both. = ( Sub MailDiscretionRequest() ' Don't forget to copy the function RangetoHTML in the module. ' Working in Office 2000-2007 Dim Rng As Range Dim OutApp As Object Dim OutMail As Object Dim StrBody As String With Application .EnableEvents = False .ScreenUpdating = False End With Set Rng = Nothing 'Set Rng = ActiveSheet.UsedRange 'You can also use a sheet name Set Rng = Sheets("DiscretionRequest").Range("DiscReq") Set OutApp = CreateObject("Outlook.Application") OutApp.Session.Logon Set OutMail = OutApp.CreateItem(0) StrBody = "Hi Colin, discretion request for your attention." & vbNewLine & vbNewLine On Error Resume Next With OutMail .To = "Dolder, Colin : Business Banking Risk" .CC = "" .BCC = "" .Subject = "DISCRETION REQUEST" .HTMLBody = RangetoHTML(Rng) '.Body = StrBody '.Send .Display End With On Error GoTo 0 With Application .EnableEvents = True .ScreenUpdating = True End With Set OutMail = Nothing Set OutApp = Nothing End Sub thanks, Scott Spence Ron de Bruin wrote: Check out this first http://www.rondebruin.nl/mail/folder3/signature.htm Must go now but get back to you this evening Let me know if the second example is working for you -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "spences10" wrote in message ups.com... Hi Ron, This is fantastic!! thank you very much. There is one thing however, I need to be able to add some text at the top of the e-mail. Oh and is it possible to add an auto signature to the mail as well? Ron de Bruin wrote: Hi spences10 Try this http://www.rondebruin.nl/mail/folder3/mail2.htm -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "spences10" wrote in message ups.com... Hi, I am trying to Copy the contents of an Excel worksheet into an Outlook mail item, like a simple copy and paste. Is there any code that can do this, I have examples that can populate from the worksheet but I need it to be in a table format. Can anyone help?? |
Pasting from Excel to Outlook with VBA code
I have already tried this and no joy.
I not see that in your code Note: I not use vbnewline and the HTMLBody line is different -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "spences10" wrote in message oups.com... Hi Ron, I have already tried this and no joy. Confused = ( Ron de Bruin wrote: Hi Scott Use this line StrBody = "Hi Colin, discretion request for your attention." & "<br<br" And this .HTMLBody = StrBody & RangetoHTML(rng) -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "spences10" wrote in message oups.com... Hi Ron, Thanks again for your help, I am struggling at the moment though. Using the code supplied by yourself I have managed to paste from the workbook into an outlook mail item, but adding a text string is proving difficult as I can only have either the pasted item or the text string but not both. = ( Sub MailDiscretionRequest() ' Don't forget to copy the function RangetoHTML in the module. ' Working in Office 2000-2007 Dim Rng As Range Dim OutApp As Object Dim OutMail As Object Dim StrBody As String With Application .EnableEvents = False .ScreenUpdating = False End With Set Rng = Nothing 'Set Rng = ActiveSheet.UsedRange 'You can also use a sheet name Set Rng = Sheets("DiscretionRequest").Range("DiscReq") Set OutApp = CreateObject("Outlook.Application") OutApp.Session.Logon Set OutMail = OutApp.CreateItem(0) StrBody = "Hi Colin, discretion request for your attention." & vbNewLine & vbNewLine On Error Resume Next With OutMail .To = "Dolder, Colin : Business Banking Risk" .CC = "" .BCC = "" .Subject = "DISCRETION REQUEST" .HTMLBody = RangetoHTML(Rng) '.Body = StrBody '.Send .Display End With On Error GoTo 0 With Application .EnableEvents = True .ScreenUpdating = True End With Set OutMail = Nothing Set OutApp = Nothing End Sub thanks, Scott Spence Ron de Bruin wrote: Check out this first http://www.rondebruin.nl/mail/folder3/signature.htm Must go now but get back to you this evening Let me know if the second example is working for you -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "spences10" wrote in message ups.com... Hi Ron, This is fantastic!! thank you very much. There is one thing however, I need to be able to add some text at the top of the e-mail. Oh and is it possible to add an auto signature to the mail as well? Ron de Bruin wrote: Hi spences10 Try this http://www.rondebruin.nl/mail/folder3/mail2.htm -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "spences10" wrote in message ups.com... Hi, I am trying to Copy the contents of an Excel worksheet into an Outlook mail item, like a simple copy and paste. Is there any code that can do this, I have examples that can populate from the worksheet but I need it to be in a table format. Can anyone help?? |
Pasting from Excel to Outlook with VBA code
Hi Ron,
i have amended the code as directed [please see below] and it still only pasts the defined area and does not add the text as a string. :-( Function TLMailDiscretionRequest() ' Don't forget to copy the function RangetoHTML in the module. ' Working in Office 2000-2007 Dim Rng As Range Dim OutApp As Object Dim OutMail As Object Dim StrBody As String With Application .EnableEvents = False .ScreenUpdating = False End With Set Rng = Nothing 'Set Rng = ActiveSheet.UsedRange 'You can also use a sheet name Set Rng = Sheets("DiscretionRequest").Range("DiscReq") Set OutApp = CreateObject("Outlook.Application") OutApp.Session.Logon Set OutMail = OutApp.CreateItem(0) StrBody = "Hi Colin, discretion request for your attention." & "<br<br" On Error Resume Next With OutMail .To = "Dolder, Colin : Business Banking Risk" .CC = "" .BCC = "" .Subject = "DISCRETION REQUEST" .HTMLBody = StrBody & RangetoHTML(Rng) '.Body = StrBody '.Send .Display End With On Error GoTo 0 With Application .EnableEvents = True .ScreenUpdating = True End With Set OutMail = Nothing Set OutApp = Nothing End Function Ron de Bruin wrote: I have already tried this and no joy. I not see that in your code Note: I not use vbnewline and the HTMLBody line is different -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "spences10" wrote in message oups.com... Hi Ron, I have already tried this and no joy. Confused = ( Ron de Bruin wrote: Hi Scott Use this line StrBody = "Hi Colin, discretion request for your attention." & "<br<br" And this .HTMLBody = StrBody & RangetoHTML(rng) -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "spences10" wrote in message oups.com... Hi Ron, Thanks again for your help, I am struggling at the moment though. Using the code supplied by yourself I have managed to paste from the workbook into an outlook mail item, but adding a text string is proving difficult as I can only have either the pasted item or the text string but not both. = ( Sub MailDiscretionRequest() ' Don't forget to copy the function RangetoHTML in the module. ' Working in Office 2000-2007 Dim Rng As Range Dim OutApp As Object Dim OutMail As Object Dim StrBody As String With Application .EnableEvents = False .ScreenUpdating = False End With Set Rng = Nothing 'Set Rng = ActiveSheet.UsedRange 'You can also use a sheet name Set Rng = Sheets("DiscretionRequest").Range("DiscReq") Set OutApp = CreateObject("Outlook.Application") OutApp.Session.Logon Set OutMail = OutApp.CreateItem(0) StrBody = "Hi Colin, discretion request for your attention." & vbNewLine & vbNewLine On Error Resume Next With OutMail .To = "Dolder, Colin : Business Banking Risk" .CC = "" .BCC = "" .Subject = "DISCRETION REQUEST" .HTMLBody = RangetoHTML(Rng) '.Body = StrBody '.Send .Display End With On Error GoTo 0 With Application .EnableEvents = True .ScreenUpdating = True End With Set OutMail = Nothing Set OutApp = Nothing End Sub thanks, Scott Spence Ron de Bruin wrote: Check out this first http://www.rondebruin.nl/mail/folder3/signature.htm Must go now but get back to you this evening Let me know if the second example is working for you -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "spences10" wrote in message ups.com... Hi Ron, This is fantastic!! thank you very much. There is one thing however, I need to be able to add some text at the top of the e-mail. Oh and is it possible to add an auto signature to the mail as well? Ron de Bruin wrote: Hi spences10 Try this http://www.rondebruin.nl/mail/folder3/mail2.htm -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "spences10" wrote in message ups.com... Hi, I am trying to Copy the contents of an Excel worksheet into an Outlook mail item, like a simple copy and paste. Is there any code that can do this, I have examples that can populate from the worksheet but I need it to be in a table format. Can anyone help?? |
Pasting from Excel to Outlook with VBA code
Which Excel version do you use ?
Can you send me the workbook private. -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "spences10" wrote in message ups.com... Hi Ron, i have amended the code as directed [please see below] and it still only pasts the defined area and does not add the text as a string. :-( Function TLMailDiscretionRequest() ' Don't forget to copy the function RangetoHTML in the module. ' Working in Office 2000-2007 Dim Rng As Range Dim OutApp As Object Dim OutMail As Object Dim StrBody As String With Application .EnableEvents = False .ScreenUpdating = False End With Set Rng = Nothing 'Set Rng = ActiveSheet.UsedRange 'You can also use a sheet name Set Rng = Sheets("DiscretionRequest").Range("DiscReq") Set OutApp = CreateObject("Outlook.Application") OutApp.Session.Logon Set OutMail = OutApp.CreateItem(0) StrBody = "Hi Colin, discretion request for your attention." & "<br<br" On Error Resume Next With OutMail .To = "Dolder, Colin : Business Banking Risk" .CC = "" .BCC = "" .Subject = "DISCRETION REQUEST" .HTMLBody = StrBody & RangetoHTML(Rng) '.Body = StrBody '.Send .Display End With On Error GoTo 0 With Application .EnableEvents = True .ScreenUpdating = True End With Set OutMail = Nothing Set OutApp = Nothing End Function Ron de Bruin wrote: I have already tried this and no joy. I not see that in your code Note: I not use vbnewline and the HTMLBody line is different -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "spences10" wrote in message oups.com... Hi Ron, I have already tried this and no joy. Confused = ( Ron de Bruin wrote: Hi Scott Use this line StrBody = "Hi Colin, discretion request for your attention." & "<br<br" And this .HTMLBody = StrBody & RangetoHTML(rng) -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "spences10" wrote in message oups.com... Hi Ron, Thanks again for your help, I am struggling at the moment though. Using the code supplied by yourself I have managed to paste from the workbook into an outlook mail item, but adding a text string is proving difficult as I can only have either the pasted item or the text string but not both. = ( Sub MailDiscretionRequest() ' Don't forget to copy the function RangetoHTML in the module. ' Working in Office 2000-2007 Dim Rng As Range Dim OutApp As Object Dim OutMail As Object Dim StrBody As String With Application .EnableEvents = False .ScreenUpdating = False End With Set Rng = Nothing 'Set Rng = ActiveSheet.UsedRange 'You can also use a sheet name Set Rng = Sheets("DiscretionRequest").Range("DiscReq") Set OutApp = CreateObject("Outlook.Application") OutApp.Session.Logon Set OutMail = OutApp.CreateItem(0) StrBody = "Hi Colin, discretion request for your attention." & vbNewLine & vbNewLine On Error Resume Next With OutMail .To = "Dolder, Colin : Business Banking Risk" .CC = "" .BCC = "" .Subject = "DISCRETION REQUEST" .HTMLBody = RangetoHTML(Rng) '.Body = StrBody '.Send .Display End With On Error GoTo 0 With Application .EnableEvents = True .ScreenUpdating = True End With Set OutMail = Nothing Set OutApp = Nothing End Sub thanks, Scott Spence Ron de Bruin wrote: Check out this first http://www.rondebruin.nl/mail/folder3/signature.htm Must go now but get back to you this evening Let me know if the second example is working for you -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "spences10" wrote in message ups.com... Hi Ron, This is fantastic!! thank you very much. There is one thing however, I need to be able to add some text at the top of the e-mail. Oh and is it possible to add an auto signature to the mail as well? Ron de Bruin wrote: Hi spences10 Try this http://www.rondebruin.nl/mail/folder3/mail2.htm -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "spences10" wrote in message ups.com... Hi, I am trying to Copy the contents of an Excel worksheet into an Outlook mail item, like a simple copy and paste. Is there any code that can do this, I have examples that can populate from the worksheet but I need it to be in a table format. Can anyone help?? |
Pasting from Excel to Outlook with VBA code
Stupid from me
If Word is your mail editor in Outlook this is not working Change it in Outlook: ToolsOptions..Mail format tab -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Ron de Bruin" wrote in message ... Which Excel version do you use ? Can you send me the workbook private. -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "spences10" wrote in message ups.com... Hi Ron, i have amended the code as directed [please see below] and it still only pasts the defined area and does not add the text as a string. :-( Function TLMailDiscretionRequest() ' Don't forget to copy the function RangetoHTML in the module. ' Working in Office 2000-2007 Dim Rng As Range Dim OutApp As Object Dim OutMail As Object Dim StrBody As String With Application .EnableEvents = False .ScreenUpdating = False End With Set Rng = Nothing 'Set Rng = ActiveSheet.UsedRange 'You can also use a sheet name Set Rng = Sheets("DiscretionRequest").Range("DiscReq") Set OutApp = CreateObject("Outlook.Application") OutApp.Session.Logon Set OutMail = OutApp.CreateItem(0) StrBody = "Hi Colin, discretion request for your attention." & "<br<br" On Error Resume Next With OutMail .To = "Dolder, Colin : Business Banking Risk" .CC = "" .BCC = "" .Subject = "DISCRETION REQUEST" .HTMLBody = StrBody & RangetoHTML(Rng) '.Body = StrBody '.Send .Display End With On Error GoTo 0 With Application .EnableEvents = True .ScreenUpdating = True End With Set OutMail = Nothing Set OutApp = Nothing End Function Ron de Bruin wrote: I have already tried this and no joy. I not see that in your code Note: I not use vbnewline and the HTMLBody line is different -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "spences10" wrote in message oups.com... Hi Ron, I have already tried this and no joy. Confused = ( Ron de Bruin wrote: Hi Scott Use this line StrBody = "Hi Colin, discretion request for your attention." & "<br<br" And this .HTMLBody = StrBody & RangetoHTML(rng) -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "spences10" wrote in message oups.com... Hi Ron, Thanks again for your help, I am struggling at the moment though. Using the code supplied by yourself I have managed to paste from the workbook into an outlook mail item, but adding a text string is proving difficult as I can only have either the pasted item or the text string but not both. = ( Sub MailDiscretionRequest() ' Don't forget to copy the function RangetoHTML in the module. ' Working in Office 2000-2007 Dim Rng As Range Dim OutApp As Object Dim OutMail As Object Dim StrBody As String With Application .EnableEvents = False .ScreenUpdating = False End With Set Rng = Nothing 'Set Rng = ActiveSheet.UsedRange 'You can also use a sheet name Set Rng = Sheets("DiscretionRequest").Range("DiscReq") Set OutApp = CreateObject("Outlook.Application") OutApp.Session.Logon Set OutMail = OutApp.CreateItem(0) StrBody = "Hi Colin, discretion request for your attention." & vbNewLine & vbNewLine On Error Resume Next With OutMail .To = "Dolder, Colin : Business Banking Risk" .CC = "" .BCC = "" .Subject = "DISCRETION REQUEST" .HTMLBody = RangetoHTML(Rng) '.Body = StrBody '.Send .Display End With On Error GoTo 0 With Application .EnableEvents = True .ScreenUpdating = True End With Set OutMail = Nothing Set OutApp = Nothing End Sub thanks, Scott Spence Ron de Bruin wrote: Check out this first http://www.rondebruin.nl/mail/folder3/signature.htm Must go now but get back to you this evening Let me know if the second example is working for you -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "spences10" wrote in message ups.com... Hi Ron, This is fantastic!! thank you very much. There is one thing however, I need to be able to add some text at the top of the e-mail. Oh and is it possible to add an auto signature to the mail as well? Ron de Bruin wrote: Hi spences10 Try this http://www.rondebruin.nl/mail/folder3/mail2.htm -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "spences10" wrote in message ups.com... Hi, I am trying to Copy the contents of an Excel worksheet into an Outlook mail item, like a simple copy and paste. Is there any code that can do this, I have examples that can populate from the worksheet but I need it to be in a table format. Can anyone help?? |
Pasting from Excel to Outlook with VBA code
thats it Ron,
thank you very much Ron de Bruin wrote: Stupid from me If Word is your mail editor in Outlook this is not working Change it in Outlook: ToolsOptions..Mail format tab -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Ron de Bruin" wrote in message ... Which Excel version do you use ? Can you send me the workbook private. -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "spences10" wrote in message ups.com... Hi Ron, i have amended the code as directed [please see below] and it still only pasts the defined area and does not add the text as a string. :-( Function TLMailDiscretionRequest() ' Don't forget to copy the function RangetoHTML in the module. ' Working in Office 2000-2007 Dim Rng As Range Dim OutApp As Object Dim OutMail As Object Dim StrBody As String With Application .EnableEvents = False .ScreenUpdating = False End With Set Rng = Nothing 'Set Rng = ActiveSheet.UsedRange 'You can also use a sheet name Set Rng = Sheets("DiscretionRequest").Range("DiscReq") Set OutApp = CreateObject("Outlook.Application") OutApp.Session.Logon Set OutMail = OutApp.CreateItem(0) StrBody = "Hi Colin, discretion request for your attention." & "<br<br" On Error Resume Next With OutMail .To = "Dolder, Colin : Business Banking Risk" .CC = "" .BCC = "" .Subject = "DISCRETION REQUEST" .HTMLBody = StrBody & RangetoHTML(Rng) '.Body = StrBody '.Send .Display End With On Error GoTo 0 With Application .EnableEvents = True .ScreenUpdating = True End With Set OutMail = Nothing Set OutApp = Nothing End Function Ron de Bruin wrote: I have already tried this and no joy. I not see that in your code Note: I not use vbnewline and the HTMLBody line is different -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "spences10" wrote in message oups.com... Hi Ron, I have already tried this and no joy. Confused = ( Ron de Bruin wrote: Hi Scott Use this line StrBody = "Hi Colin, discretion request for your attention." & "<br<br" And this .HTMLBody = StrBody & RangetoHTML(rng) -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "spences10" wrote in message oups.com... Hi Ron, Thanks again for your help, I am struggling at the moment though. Using the code supplied by yourself I have managed to paste from the workbook into an outlook mail item, but adding a text string is proving difficult as I can only have either the pasted item or the text string but not both. = ( Sub MailDiscretionRequest() ' Don't forget to copy the function RangetoHTML in the module. ' Working in Office 2000-2007 Dim Rng As Range Dim OutApp As Object Dim OutMail As Object Dim StrBody As String With Application .EnableEvents = False .ScreenUpdating = False End With Set Rng = Nothing 'Set Rng = ActiveSheet.UsedRange 'You can also use a sheet name Set Rng = Sheets("DiscretionRequest").Range("DiscReq") Set OutApp = CreateObject("Outlook.Application") OutApp.Session.Logon Set OutMail = OutApp.CreateItem(0) StrBody = "Hi Colin, discretion request for your attention." & vbNewLine & vbNewLine On Error Resume Next With OutMail .To = "Dolder, Colin : Business Banking Risk" .CC = "" .BCC = "" .Subject = "DISCRETION REQUEST" .HTMLBody = RangetoHTML(Rng) '.Body = StrBody '.Send .Display End With On Error GoTo 0 With Application .EnableEvents = True .ScreenUpdating = True End With Set OutMail = Nothing Set OutApp = Nothing End Sub thanks, Scott Spence Ron de Bruin wrote: Check out this first http://www.rondebruin.nl/mail/folder3/signature.htm Must go now but get back to you this evening Let me know if the second example is working for you -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "spences10" wrote in message ups.com... Hi Ron, This is fantastic!! thank you very much. There is one thing however, I need to be able to add some text at the top of the e-mail. Oh and is it possible to add an auto signature to the mail as well? Ron de Bruin wrote: Hi spences10 Try this http://www.rondebruin.nl/mail/folder3/mail2.htm -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "spences10" wrote in message ups.com... Hi, I am trying to Copy the contents of an Excel worksheet into an Outlook mail item, like a simple copy and paste. Is there any code that can do this, I have examples that can populate from the worksheet but I need it to be in a table format. Can anyone help?? |
Pasting from Excel to Outlook with VBA code
Hi Ron,
Last question [I think], now that I can send both the pasted area from excel and some text. is there any way I can ensure that this happens for other users, or will they all have to change their Outlook settings as I have? spences10 wrote: thats it Ron, thank you very much Ron de Bruin wrote: Stupid from me If Word is your mail editor in Outlook this is not working Change it in Outlook: ToolsOptions..Mail format tab -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Ron de Bruin" wrote in message ... Which Excel version do you use ? Can you send me the workbook private. -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "spences10" wrote in message ups.com... Hi Ron, i have amended the code as directed [please see below] and it still only pasts the defined area and does not add the text as a string. :-( Function TLMailDiscretionRequest() ' Don't forget to copy the function RangetoHTML in the module. ' Working in Office 2000-2007 Dim Rng As Range Dim OutApp As Object Dim OutMail As Object Dim StrBody As String With Application .EnableEvents = False .ScreenUpdating = False End With Set Rng = Nothing 'Set Rng = ActiveSheet.UsedRange 'You can also use a sheet name Set Rng = Sheets("DiscretionRequest").Range("DiscReq") Set OutApp = CreateObject("Outlook.Application") OutApp.Session.Logon Set OutMail = OutApp.CreateItem(0) StrBody = "Hi Colin, discretion request for your attention." & "<br<br" On Error Resume Next With OutMail .To = "Dolder, Colin : Business Banking Risk" .CC = "" .BCC = "" .Subject = "DISCRETION REQUEST" .HTMLBody = StrBody & RangetoHTML(Rng) '.Body = StrBody '.Send .Display End With On Error GoTo 0 With Application .EnableEvents = True .ScreenUpdating = True End With Set OutMail = Nothing Set OutApp = Nothing End Function Ron de Bruin wrote: I have already tried this and no joy. I not see that in your code Note: I not use vbnewline and the HTMLBody line is different -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "spences10" wrote in message oups.com... Hi Ron, I have already tried this and no joy. Confused = ( Ron de Bruin wrote: Hi Scott Use this line StrBody = "Hi Colin, discretion request for your attention." & "<br<br" And this .HTMLBody = StrBody & RangetoHTML(rng) -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "spences10" wrote in message oups.com... Hi Ron, Thanks again for your help, I am struggling at the moment though. Using the code supplied by yourself I have managed to paste from the workbook into an outlook mail item, but adding a text string is proving difficult as I can only have either the pasted item or the text string but not both. = ( Sub MailDiscretionRequest() ' Don't forget to copy the function RangetoHTML in the module. ' Working in Office 2000-2007 Dim Rng As Range Dim OutApp As Object Dim OutMail As Object Dim StrBody As String With Application .EnableEvents = False .ScreenUpdating = False End With Set Rng = Nothing 'Set Rng = ActiveSheet.UsedRange 'You can also use a sheet name Set Rng = Sheets("DiscretionRequest").Range("DiscReq") Set OutApp = CreateObject("Outlook.Application") OutApp.Session.Logon Set OutMail = OutApp.CreateItem(0) StrBody = "Hi Colin, discretion request for your attention." & vbNewLine & vbNewLine On Error Resume Next With OutMail .To = "Dolder, Colin : Business Banking Risk" .CC = "" .BCC = "" .Subject = "DISCRETION REQUEST" .HTMLBody = RangetoHTML(Rng) '.Body = StrBody '.Send .Display End With On Error GoTo 0 With Application .EnableEvents = True .ScreenUpdating = True End With Set OutMail = Nothing Set OutApp = Nothing End Sub thanks, Scott Spence Ron de Bruin wrote: Check out this first http://www.rondebruin.nl/mail/folder3/signature.htm Must go now but get back to you this evening Let me know if the second example is working for you -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "spences10" wrote in message ups.com... Hi Ron, This is fantastic!! thank you very much. There is one thing however, I need to be able to add some text at the top of the e-mail. Oh and is it possible to add an auto signature to the mail as well? Ron de Bruin wrote: Hi spences10 Try this http://www.rondebruin.nl/mail/folder3/mail2.htm -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "spences10" wrote in message ups.com... Hi, I am trying to Copy the contents of an Excel worksheet into an Outlook mail item, like a simple copy and paste. Is there any code that can do this, I have examples that can populate from the worksheet but I need it to be in a table format. Can anyone help?? |
Pasting from Excel to Outlook with VBA code
or will they all have to change their Outlook settings
Yes. If you can change this setting (if possible with code, I never try) and users like the Word interface to edit mail then I don't think they want you to change this setting. -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "spences10" wrote in message ups.com... Hi Ron, Last question [I think], now that I can send both the pasted area from excel and some text. is there any way I can ensure that this happens for other users, or will they all have to change their Outlook settings as I have? spences10 wrote: thats it Ron, thank you very much Ron de Bruin wrote: Stupid from me If Word is your mail editor in Outlook this is not working Change it in Outlook: ToolsOptions..Mail format tab -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Ron de Bruin" wrote in message ... Which Excel version do you use ? Can you send me the workbook private. -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "spences10" wrote in message ups.com... Hi Ron, i have amended the code as directed [please see below] and it still only pasts the defined area and does not add the text as a string. :-( Function TLMailDiscretionRequest() ' Don't forget to copy the function RangetoHTML in the module. ' Working in Office 2000-2007 Dim Rng As Range Dim OutApp As Object Dim OutMail As Object Dim StrBody As String With Application .EnableEvents = False .ScreenUpdating = False End With Set Rng = Nothing 'Set Rng = ActiveSheet.UsedRange 'You can also use a sheet name Set Rng = Sheets("DiscretionRequest").Range("DiscReq") Set OutApp = CreateObject("Outlook.Application") OutApp.Session.Logon Set OutMail = OutApp.CreateItem(0) StrBody = "Hi Colin, discretion request for your attention." & "<br<br" On Error Resume Next With OutMail .To = "Dolder, Colin : Business Banking Risk" .CC = "" .BCC = "" .Subject = "DISCRETION REQUEST" .HTMLBody = StrBody & RangetoHTML(Rng) '.Body = StrBody '.Send .Display End With On Error GoTo 0 With Application .EnableEvents = True .ScreenUpdating = True End With Set OutMail = Nothing Set OutApp = Nothing End Function Ron de Bruin wrote: I have already tried this and no joy. I not see that in your code Note: I not use vbnewline and the HTMLBody line is different -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "spences10" wrote in message oups.com... Hi Ron, I have already tried this and no joy. Confused = ( Ron de Bruin wrote: Hi Scott Use this line StrBody = "Hi Colin, discretion request for your attention." & "<br<br" And this .HTMLBody = StrBody & RangetoHTML(rng) -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "spences10" wrote in message oups.com... Hi Ron, Thanks again for your help, I am struggling at the moment though. Using the code supplied by yourself I have managed to paste from the workbook into an outlook mail item, but adding a text string is proving difficult as I can only have either the pasted item or the text string but not both. = ( Sub MailDiscretionRequest() ' Don't forget to copy the function RangetoHTML in the module. ' Working in Office 2000-2007 Dim Rng As Range Dim OutApp As Object Dim OutMail As Object Dim StrBody As String With Application .EnableEvents = False .ScreenUpdating = False End With Set Rng = Nothing 'Set Rng = ActiveSheet.UsedRange 'You can also use a sheet name Set Rng = Sheets("DiscretionRequest").Range("DiscReq") Set OutApp = CreateObject("Outlook.Application") OutApp.Session.Logon Set OutMail = OutApp.CreateItem(0) StrBody = "Hi Colin, discretion request for your attention." & vbNewLine & vbNewLine On Error Resume Next With OutMail .To = "Dolder, Colin : Business Banking Risk" .CC = "" .BCC = "" .Subject = "DISCRETION REQUEST" .HTMLBody = RangetoHTML(Rng) '.Body = StrBody '.Send .Display End With On Error GoTo 0 With Application .EnableEvents = True .ScreenUpdating = True End With Set OutMail = Nothing Set OutApp = Nothing End Sub thanks, Scott Spence Ron de Bruin wrote: Check out this first http://www.rondebruin.nl/mail/folder3/signature.htm Must go now but get back to you this evening Let me know if the second example is working for you -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "spences10" wrote in message ups.com... Hi Ron, This is fantastic!! thank you very much. There is one thing however, I need to be able to add some text at the top of the e-mail. Oh and is it possible to add an auto signature to the mail as well? Ron de Bruin wrote: Hi spences10 Try this http://www.rondebruin.nl/mail/folder3/mail2.htm -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "spences10" wrote in message ups.com... Hi, I am trying to Copy the contents of an Excel worksheet into an Outlook mail item, like a simple copy and paste. Is there any code that can do this, I have examples that can populate from the worksheet but I need it to be in a table format. Can anyone help?? |
| All times are GMT +1. The time now is 10:31 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com