JeppeBalling
04-13-2018, 05:20 AM
Hi.
I just startet using VBA and made a automatic email reminder in VBA.
The only thing i need for the code to be done, is how to automatically copy a specific Row from my excel-table and paste it into the body of the email. The specific row contains an "action" that the receiver need to work with.
My VBA module looks like this:
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("PIPELINE").Cells(Rows.Count, 14).End(xlUp).row
For x = 63 To LastRow
mydate1 = Cells(x, 9).Value
mydate2 = mydate1
Cells(x, 12).Value = mydate2
datetoday1 = Date
datetoday2 = datetoday1
Cells(x, 13).Value = datetoday2
If mydate2 - datetoday2 <= 30 Then
Set myApp = New Outlook.Application
Set mymail = myApp.CreateItem(olMailItem)
mymail.To = Cells(x, 8).Value
With mymail
.Subject = "PIPELINE Reminder!"
.Body = "Do the following task"
.Send
End With
Cells(x, 10) = "Yes"
Cells(x, 10).Font.Bold = True
Cells(x, 11).Value = mydate2 - datetoday2
End If
Next
Set myApp = Nothing
Set mymail = Nothing
End Sub
How somebody can help me.
- Jeppe
I just startet using VBA and made a automatic email reminder in VBA.
The only thing i need for the code to be done, is how to automatically copy a specific Row from my excel-table and paste it into the body of the email. The specific row contains an "action" that the receiver need to work with.
My VBA module looks like this:
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("PIPELINE").Cells(Rows.Count, 14).End(xlUp).row
For x = 63 To LastRow
mydate1 = Cells(x, 9).Value
mydate2 = mydate1
Cells(x, 12).Value = mydate2
datetoday1 = Date
datetoday2 = datetoday1
Cells(x, 13).Value = datetoday2
If mydate2 - datetoday2 <= 30 Then
Set myApp = New Outlook.Application
Set mymail = myApp.CreateItem(olMailItem)
mymail.To = Cells(x, 8).Value
With mymail
.Subject = "PIPELINE Reminder!"
.Body = "Do the following task"
.Send
End With
Cells(x, 10) = "Yes"
Cells(x, 10).Font.Bold = True
Cells(x, 11).Value = mydate2 - datetoday2
End If
Next
Set myApp = Nothing
Set mymail = Nothing
End Sub
How somebody can help me.
- Jeppe