-
May 22nd, 2019, 08:11 AM
#1
Thread Starter
Addicted Member
-
May 22nd, 2019, 04:04 PM
#2
Re: Send table on email
if word is your email editor you can paste the table into word
otherwise you need to build the table as an html string
i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next
dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part
come back and mark your original post as resolved if your problem is fixed
pete
-
May 22nd, 2019, 04:19 PM
#3
Thread Starter
Addicted Member
Re: Send table on email
Hello,
Don’t want it on word. Needs to be pasted onto an email and to a set group of people
-
May 22nd, 2019, 04:20 PM
#4
Thread Starter
Addicted Member
Re: Send table on email
Hello,
Don’t want it on word. Needs to be pasted onto an email and to a set group of people
-
May 23rd, 2019, 04:55 AM
#5
Re: Send table on email
afaik you can not paste into an outlook inspector using code, it may be possible using a bunch of API calls, but it is not something i have done, or is available within the outlook object model
how is your skill with html code?
i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next
dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part
come back and mark your original post as resolved if your problem is fixed
pete
-
May 23rd, 2019, 05:20 AM
#6
Thread Starter
Addicted Member
Re: Send table on email
Not great... :-)
I did some work on this last night and came up with the below
Its coming up with an error message which I cant seem to fix.
---------------------------
Microsoft Visual Basic for Applications
---------------------------
Compile error:
Sub or Function not defined
----------
RangetoHTML - This is the line it breaks on.
VB6 Code:
Dim rng As Range Dim OutApp As Object Dim OutMail As Object Set rng = Nothing On Error Resume Next Set rng = Selection.Range(xlCellTypeVisible) t Set rng = Sheets("Summary").Range("B4:L34").SpecialCells(xlCellTypeVisible) On Error GoTo 0 If rng Is Nothing Then MsgBox "The selection is not a range or the sheet is protected" & _ vbNewLine & "please correct and try again.", vbOKOnly Exit Sub End If With Application .EnableEvents = False .ScreenUpdating = False End With Set OutApp = CreateObject("Outlook.Application") OutApp.Session.Logon Set OutMail = OutApp.CreateItem(0) On Error Resume Next strHtml = "<html>" & "<body>" & "Hi All," & "<br>" & "</br>" & "</body>" & "</html>" With OutMail .To = "" .CC = "" .Subject = "Test Mail""" .HTMLBody = strHtml & RangetoHTML(rng) .Display End With On Error GoTo 0 With Application .EnableEvents = True .ScreenUpdating = True End With Set OutMail = Nothing Set OutApp = Nothing ActiveWorkbook.CheckCompatibility = False ActiveWorkbook.Save End Sub
Last edited by FunkyDexter; May 24th, 2019 at 02:39 AM.
-
May 23rd, 2019, 05:44 AM
#7
Re: Send table on email
.HTMLBody = strHtml & RangetoHTML(rng)
this would add the table after end of the html body
do you have some function that converts a range of cells to html?
ELSE
you can not assign a range to the string, you would need to get the value from each cell in the range and insert as a able with <TR anf <TD for rows and columns, plus any formatting required for text, alignment or cell colour
Its coming up with an error message which I cant seem to fix.
that is terrible, i wonder what the error could be, and where it would occur
i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next
dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part
come back and mark your original post as resolved if your problem is fixed
pete
-
May 23rd, 2019, 10:18 AM
#8
Thread Starter
Addicted Member
Re: Send table on email
Sorry never done this type of coding before.
Why do I need to convert a range? I only want to copy it the range specified and paste into an email.
.HTMLBody = strHtml & RangetoHTML(rng)
this would add the table after end of the html body
do you have some function that converts a range of cells to html?
-
May 23rd, 2019, 04:19 PM
#9
Re: Send table on email
I only want to copy it the range specified and paste into an email.
as stated before, i do not believe you can post into an email body by code,
you could copy the range to the clipboard, create an email then display it for the user to manually paste the clipboard content to the email, but i have never done it that way
do you have some function that converts a range of cells to html?
while it would not be too hard to do, i am sure there could
be some already available, a quick search brought up many hits, try rondebruin.nl, but his site is down at the momont
i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next
dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part
come back and mark your original post as resolved if your problem is fixed
pete
-
May 24th, 2019, 02:39 AM
#10
The best argument against democracy is a five minute conversation with the average voter - Winston Churchill
Hadoop actually sounds more like the way they greet each other in Yorkshire - Inferrd
-
May 24th, 2019, 03:20 AM
#11
Re: Send table on email
Originally Posted by kris01
Sorry never done this type of coding before.
See if this help you
VBA - Copy From Excel into Outlook
https://www.mrexcel.com/forum/excel-...o-outlook.html
-
May 24th, 2019, 06:00 AM
#12
Re: Send table on email
ok, i tested this it worked for me, but i make no promises
Code:
Set ol = GetObject(, "outlook.application")
Set msg = ol.createitem(0)
msg.Subject = "Please find enclosed yesterdays " & Format(Date - 1, "dd-mmm-yy ")
msg.to = "me"
msg.htmlbody = "hi all <br><br>"
msg.display
Range("a1:d20").Copy ' change range to suit
Application.Wait DateAdd("s", 2, Now)
SendKeys "{tab}", True
SendKeys "{DOWN}", True
SendKeys "~", True
SendKeys "%{e}p", True
msg.send
Application.CutCopyMode = False
if sendkeys gives a permission denied error message, try application.sendkeys, or wscript.shell sendkeys method
Last edited by westconn1; May 24th, 2019 at 06:24 AM.
i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next
dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part
come back and mark your original post as resolved if your problem is fixed
pete
-
May 24th, 2019, 06:16 AM
#13
Thread Starter
Addicted Member
Re: Send table on email
Thanks for doing this. Ive learnt so much from this thread.
What line do I add If i want to select a range on a particular worksheet - Worksheet "Summary"?
-
May 24th, 2019, 06:22 AM
#14
Re: Send table on email
Code:
Range("a1:d20").Copy ' change range to suit
You would qualify this, something like:
Code:
wsSummary.Range("a1:d20").Copy ' change range to suit
You'd need to set wsSummary first.
-
May 24th, 2019, 06:27 AM
#15
Re: Send table on email
change Range("a1:d20").Copy ' change range to suit
to
Code:
sheets("Summary").range("b4:l34").copy
or whatever range of cells
also i changed the date in the code to be yesterdays, rather than todays
i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next
dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part
come back and mark your original post as resolved if your problem is fixed
pete
-
May 24th, 2019, 07:29 AM
#16
Thread Starter
Addicted Member
Re: Send table on email
Hello.
Nearly there.
It brings up the email with the various components.... Subject etc etc
But not the table.
Here's my code, just in case I've missed anything.....
Sub snwb()
Set ol = GetObject(, "outlook.application")
Set msg = ol.createitem(0)
msg.Subject = "Please find enclosed yesterdays " & Format(Date, "dd-mmm-yy ")
msg.to = "me"
msg.htmlbody = "hi all <br><br>"
msg.display
Sheets("Summary").Range("b4:l34").Copy
Application.Wait DateAdd("s", 2, Now)
SendKeys "{tab}", True
SendKeys "{DOWN}", True
SendKeys "~", True
SendKeys "%{e}p", True
msg.display
Application.CutCopyMode = False
End Sub
-
May 24th, 2019, 07:50 AM
#17
Re: Send table on email
i made some small change
Code:
Set ol = GetObject(, "outlook.application")
Set msg = ol.createitem(0)
msg.Subject = "Please find enclosed yesterdays " & Format(Date, "dd-mmm-yy ")
msg.to = "me"
msg.htmlbody = "hi all <br><br>"
msg.display
Range("a1:d20").Copy ' change range to suit
Application.Wait DateAdd("s", 2, Now)
SendKeys "{tab}", True
SendKeys "{DOWN}"
SendKeys "~"
SendKeys "%(ep)", True
msg.send
Application.CutCopyMode = False
i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next
dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part
come back and mark your original post as resolved if your problem is fixed
pete
-
May 24th, 2019, 08:14 AM
#18
Thread Starter
Addicted Member
Re: Send table on email
are missing the sheet's "summary" reference from the above?
-
May 24th, 2019, 05:35 PM
#19
Re: Send table on email
i don't have a sheet summary so i just test with the activesheet change to suit, the small changes to the code were in the sendkeys
if you think that using the summary sheet might be a problem, test with some other range first, but i doubt it would make any difference
i also did not include the edit i made to the date, as i only changed that in the previous post, not my sample code
if it still not working, i will test again tonight on a later computer /office version, just in case there is any difference
i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next
dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part
come back and mark your original post as resolved if your problem is fixed
pete
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|