outlook code sync

Veeru106 · Aug 2nd 2018

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

Same question has been posted in another forum as well, if got any reply will post here for benefit of others

**Ger Plante** · Aug 3rd 2018

Forget about "generateemail" Sub. You needed to wrap your strBody string in properly formatted HTML code... You then append that HTML string to the RangetoHTML conversion as part of .HTMLbody mehtod.... here is the code:

HTML

Sub Mail_Selection_Range_Outlook_Body()


Dim rng As Range
Dim OutApp As Object
Dim OutMail As Object
Dim strBody 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




With Application
.EnableEvents = False
.ScreenUpdating = False
End With


strBody = "<html><body>Hi Res,<br><br>Please find snapshot below for FTEs submitted. Please Decline by pressing control and <a href=""mailto:[email protected]?Subject=BLR%20FTEs%20has%20been%20declined,%20Please%20specify%20reason/s"" target=""_top"">clicking here</a><br><br>Please Approve by pressing control and <a href=""mailto:[email protected]?Subject=FTEs%20has%20been%20approved"" target=""_top"">clicking here</a><br><br>Thanks.</body></head>"






Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)
On Error Resume Next


With OutMail
.To = "[email protected]"
.CC = ""
.BCC = ""
.Subject = "BLR FTEs Submission"
.htmlbody = strBody & RangetoHTML(rng)
'.strBody = Selection.Paste 'dont need this.
.Display 'or use .Send
End With
On Error GoTo 0


With Application
.EnableEvents = True
.ScreenUpdating = True
End With


Set OutMail = Nothing
Set OutApp = Nothing


End Sub

Display More

Ger

Veeru106 · Aug 5th 2018

Thansk Ger Plante...It is working perfectly....Thanks a ton buddy...

Veeru106 · Aug 6th 2018

Just one thng more...i want to add attachment as well so i have added strlocation as string and then givning "strlocation = "C:\Users\VVA0\Desktop\New folder\Raw Sheet.xlsm" below strbody but it not picking it up.....please suggest what is wrong here

**Ger Plante** · Aug 7th 2018

Without seeing your current code, its impossible to say, and also not sure what you mean by "not picking it up"... is it a compile error, or just not doing what you expected...? Anyway, the code you need is using a method called .attachments.add

See Ron's site -
https://www.rondebruin.nl/win/s1/outlook/amail6.htm

Using the last piece of code I Pasted above

It would probably look like this:

Code

With OutMail


.To = "[email protected]"


.CC = ""


.BCC = ""


.Subject = "BLR FTEs Submission"


.htmlbody = strBody & RangetoHTML(rng)


'.strBody = Selection.Paste 'dont need this.


strlocation = "C:\Users\VVA0\Desktop\New folder\Raw Sheet.xlsm"


.attachments.add strlocation


.Display 'or use .Send


End With

Display More

THIS IS UNTESTED CODE.

But take a read of Ron's page above to see how to add the attachments based on the file name being stored in a cell.

Ger

Veeru106 · Aug 7th 2018

Yes got your point....thank you

outlook code sync

Participate now!

Share