Outlook question

[Veeru106]

Hi Folks,


I need some suggestions on one situation.


I have outlook code which by clicking display new mail with Body, subject and mail ids on it.


I need suggestions on below 2 points:-

1. Is it possible to add certain table snapshot (Printscreen) in the mail
2. Is it possible to add hyperlink in the mail.

So every time, user click the button in excel, it will generate mail with snapshot of table and 2 hyperlinks called “Approve and decline”


Please suggest some ideas.


Thanks

 

[Veeru106]

For the first point I have figured out, yes we can show hyperlink in mail


But my code is showing it as link, where we want it to be one word only.


Can anyone suggest how we can change it to one word.


Currently I am using below code


strbody = "Hi ," & vbNewLine & vbNewLine & _

"Please find snapshot below for FTEs submitted" & vbNewLine & _

"" & vbNewLine & _

"Please Decline by pressing control and clicking here=mailto:raje.raj@gmail.com?subject=BLR%20FTEs%20has%20been%20declined,%20Please%20specify%20reason/s" & vbNewLine & _

"" & vbNewLine & _

"Thanks"

End With

 

[Veeru106]

Hi,


I have 2 separate codes, extension to the earlier post.


First (Generate Mail), will open outlook mail and insert some text and hyperlink to it ( Still looking for a way to convert link to one word text)


And second one (by Ron Debruin) which paste excel table into mail.


How can we combine these 2 so that we have complete mail with text, hyperlinks from first code and table from second code.


Please suggest, I am sure lot of folks looking out for this solution.


Attaching file as well.


Thanks in advance

 

[Veeru106]

Same question raised on one other forum...will update members if get any solution there....Thank you

 

[Stevie]

Hi Veeru106,
you can just use markup for your email body, and set the htmlbody attribute to strbody instead of the body attribute.
To do this, just write your email in html.
For your example, it would mean changing your first macro to something like this:

Sub Generatemail()
Dim OutApp As Object
Dim OutMail As Object
Dim strbody As String
Dim NewWB As Workbook
Set OutApp = CreateObject("outlook.application")
Set OutMail = OutApp.CreateItem(0)
With NewWB
strbody = "Hi Res,<br /><br />Please find snapshot below for FTEs submitted<br /><br />" & _
"Please Decline by pressing control and clicking <a href=""mailto:raje.raj@gmail.com?subject=BLR%20FTEs%20has%20been%20declined,%20Please%20specify%20reason/s"">here</a><br /><br />" & _
"Please Approve by pressing control and clicking <a href=""mailto:raje.raj@gmail.com?subject=FTEs%20has%20been%20approved"">here</a><br />" & _
"Thanks"
End With
With OutMail
.To = "res.san@gmail.com"
.CC = ""
.BCC = ""
.Subject = "BLR FTE Submission"
.htmlBody = strbody
.Display
End With
Set OutMail = Nothing
Set OutApp = Nothing
With Application
.ScreenUpdating = True
.EnableEvents = True
End With
End Sub

This will also make it easier to combine Ron Debruin's RangeToHTML function with your code, as that basically creates an HTML file from your range, so just copy that into your body where you want it.

If this was helpful, please click 'Like'!

Stevie

EDIT: I have only changed the code to make the links work how you requested. I'm not sure what the purpose of some of your code is as it looks a little redundant, (e.g. with NewWB'), but I assume it is required, so I left it in.

 

[Veeru106]

Hi Stevie,


Thanks for taking time out and looking into this.


it is working fine except it is not copying and pasting data from summary tab , which it should be as per Ron Debruin code.


I tried to make some changes but it gives me error of “End With without With” and then I also tried to add one piece where we can add attachment as well, but unfortunately that is also not working


Can you please again look into this

Sub Generatemail()
Dim OutApp As Object
Dim OutMail As Object
Dim strbody As String
Dim strlocation As String

Set rng = Nothing
On Error Resume Next
'Only the visible cells in the selection
'Set rng = Selection.SpecialCells(xlCellTypeVisible)
'You can also use a fixed range if you want
Set rng = Sheets("Summary").Range("A1:Z20").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
Set OutApp = CreateObject("outlook.application")
Set OutMail = OutApp.CreateItem(0)
strlocation = "C:\Users\VVA087\Desktop\BLR Automation\Combine Macro.xlsm"

strbody = "Hi Res,<br /><br />Please find snapshot below for FTEs submitted<br /><br />" & _
"Please Decline by pressing control and clicking <a href=""mailto:raje.raj@gmail.com?subject=BLR%20FTEs%20has%20been%20declined,%20Please%20specify%20reason/s"">here</a><br /><br />" & _
"Please Approve by pressing control and clicking <a href=""mailto:raje.raj@gmail.com?subject=FTEs%20has%20been%20approved"">here</a><br />" & _
"Thanks"
End With
With OutMail
.To = "res.san@gmail.com"
.CC = ""
.BCC = ""
.Subject = "BLR FTE Submission"
.htmlBody = strbody
.Display
End With
Set OutMail = Nothing
Set OutApp = Nothing
With Application
.ScreenUpdating = True
.EnableEvents = True
End With
End Sub

 

[Stevie]

Hi Veeru106,
As I said, you should use Ron Debruin's RangeToHTML function which you had in your workbook.
You store the data in a range variable with this line:

Set rng = Sheets("Summary").Range("A1:Z20").SpecialCells(xlCellTypeVisible)

But you don't ever then put that into the RangeToHTML function in the code you have pasted above.
Do that, then append that to your

.htmlBody = strbody

line.

Stevie

 

[Veeru106]

I got you Stevie....Thanks for your time