Hi All - finally, I found a post that says something about html and after many failed attempts my macro finally gave me the right output. ☺️
Good day everyone.
Anyone there who knows how to preserve the link after you do copy and paste of hyperlink function?
My report originally contains two columns: Report ID and Report Link. I added a column in between and type in the formula hyperlink(URL,friendly_name).
I just noticed that after I copied and pasted the column on the same sheet, the links are not working anymore. My goal is for my report to only have a column ID that once it's clicked, the url associated with that ID would open.
Thanks as well chavezm3. The "Join" worked (part of the code below). Now I have three columns in my "Reports Recipients" worksheet: A: Reports Name (names are same with worksheet names), B: Recipients on To field, C: Recipient on Cc field. I am trying to accomplish looping through column A, so as long as it's same with my active sheet, the To and CC will continue to be copied. Would that be possible with this "Join"? I will post additional code if I have positive results -cross finger
With ActiveWorkbook.Worksheets("Reports Recipients")
PATo = Join(Application.Transpose(Sheet5.Range(Sheet5.Range("B2"), Sheet5.Range("B2").End(xlDown)).Value), "; ")
PACc = Join(Application.Transpose(Sheet5.Range(Sheet5.Range("C2"), Sheet5.Range("C2").End(xlDown)).Value), "; ")
Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)
.To = PATo
.CC = PACc
.Subject = "My Report" & Format(Now(), "MMMM YYYY")
.htmlbody = HTMLBody
strLocation = ThisWorkbook.Path & "" & sFilename & UCase(Format(Now(), "YYYY-MMM-DD")) & ".xlsx"
On Error GoTo 0
Set OutMail = Nothing
Set OutApp = Nothing
Thank you for helping me. Your code helps me check correponding cells if not empty. However, going back to my first note, I would like to send the email to all reports recipients included in the distrolist instead of hard-coding it, so that when contacts change from time to time, user will just edit the distrolist and not the macro itself.
I hope you could accomodate this question, thank you in advance.
I am trying to write a macro code that will email the recipients based on condition. That is, I have different worksheets in a workbook and each is named according to the report name (except the reports recipients sheet because this serves as reference when emailing recipients).
If for example my active worksheet is reportname1, and my macro here will open outlook and email recipients, how can I set the recipients to the values on column B of reports recipients sheet while column A is equal to reportname1 (the active sheet)? Note that I would also like to set column C for Cc field and column D on Bcc field while not empty.
I hope I was able to give clear details, please let me know if otherwise.
Again, thank you.