AndreaWorkPlace
New Member
- Joined
- Jan 24, 2022
- Messages
- 5
- Office Version
- 365
- Platform
- Windows
- Web
HI All.
Apologies if this has been answered but I have been working on this for two days solid now and can's seem to find a solution. I understand its my own lack of experience but and help would be really appreciated. I have written a macro that emails automatically out to someone when a particular date is in 30 days time. The title is populated with the name of the file and the original spreadsheet is updated to show an email has been sent. This all works perfectly! However, I would like to add a link in the body of the email that the person receiving it, can click on to get to the document. I have attached my code and marked in red what I think it should be but evidently it is wrong. My email is still generated but just with the bit of code. I think I need to use the String functionality somewhere but cant seem how to do it. The "13" referred to in this piece of code ie cells (x, 13) is the column number I have put the hyperlink in and should relate to that row. Many thanks everyone!!!!!!!!!
Apologies if this has been answered but I have been working on this for two days solid now and can's seem to find a solution. I understand its my own lack of experience but and help would be really appreciated. I have written a macro that emails automatically out to someone when a particular date is in 30 days time. The title is populated with the name of the file and the original spreadsheet is updated to show an email has been sent. This all works perfectly! However, I would like to add a link in the body of the email that the person receiving it, can click on to get to the document. I have attached my code and marked in red what I think it should be but evidently it is wrong. My email is still generated but just with the bit of code. I think I need to use the String functionality somewhere but cant seem how to do it. The "13" referred to in this piece of code ie cells (x, 13) is the column number I have put the hyperlink in and should relate to that row. Many thanks everyone!!!!!!!!!
VBA Code:
Sub datesexcelvba()
Dim myApp As Outlook.Application, mymail As Outlook.MailItem
Dim mydate1 As Date
Dim mydate2 As Long
Dim datetoday1 As Date
Dim datetoday2 As Long
Dim x As Long
lastrow = Sheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Row
For x = 2 To lastrow
mydate1 = Cells(x, 8).Value
mydate2 = mydate1
Cells(x, 11).Value = mydate2
datetoday1 = Date
datetoday2 = datetoday1
Cells(x, 12).Value = datetoday2
If mydate2 - datetoday2 = 30 Then
Set myApp = New Outlook.Application
Set mymail = myApp.CreateItem(olmailitem)
mymail.To = Cells(x, 7).Value
With mymail
.Subject = "Review Reminder " & Cells(x, 4).Value
.Body = "Good Morning," & vbCrLf & _
"Please be aware that this file is due for review in the next 30 days" & vbCrLf & _
"Regards" & vbCrLf & _
"Master Control Document"
[COLOR=rgb(209, 72, 65)]' "Please click " & " <a href = Sheets (Sheet1).Cells (x, 13).Value "">Here</A >"[/COLOR]
.Display
'.send
End With
Set myApp = Nothing
Set mymail = Nothing
Cells(x, 9) = "Yes"
Cells(x, 9).Font.ColorIndex = 3
Cells(x, 9).Font.Bold = True
Cells(x, 10).Value = mydate2 - datetoday2
End If
Next
Set myApp = Nothing
Set mymail = Nothing
End Sub
