Need more help...

[Mindy2017]

I recently created a spreadsheet that will notify me of upcoming offsite meetings by two weeks.

Here is a partial code

With OutLookMailItem
.to = MailDest1
.bcc = MailDest2
.Subject = Range("a1").Value
.Body = Range("B1").Value

I like the subject line to include the following: A Gentle Reminder: Boston (which ever location is within that 2 week notice.)

[TABLE="width: 343"]
<colgroup><col></colgroup><tbody>[TR]
[TD][TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]a[/TD]
[TD] b[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]A Gentle Reminder:<strike></strike>
[/TD]
[TD]<strike></strike>
[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Boston[/TD]
[TD]8/15/2018[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]New York[/TD]
[TD]8/20/2018[/TD]
[/TR]
</tbody>[/TABLE]

[/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[/TR]
[TR]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[mrshl9898]

Something like this? For A Gentle Reminder: Boston, New York

Sub example()


Dim rownum As Long
Dim mystr As String


rownum = 2
Do Until Cells(rownum, 2).Value = ""
If Cells(rownum, 2).Value <= Date + 14 Then
mystr = mystr + Cells(rownum, 1).Value & ", "
End If
rownum = rownum + 1
Loop


mystr = Left(mystr, Len(mystr) - 2)


With OutLookMailItem
.to = MailDest1
.bcc = MailDest2
.Subject = Range("a1").Value & " " & mystr
.Body = Range("B1").Value




End Sub

 

[Mindy2017]

First and foremost, thank you for this code. It is in the right direction but unfortunately it is pulling everyone name on the list. What I was hoping for is that It would only pull anything that had "Send Reminder" in column (let say C).


Basically the subject line should read "This is a friendly reminder (anything that has Send reminder in the column).

I was trying to past a picture but it would not let me.

So my current code:
With OutLookMailItem
.to = MailDest1
.bcc = MailDest2
.Subject = Range("a1").Value (only pulls "Friendly Reminder") I would like it to also pull anything that come up "Send Remember".
.Body = Range("B1").Value
'.send


[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Friendly Reminder[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Location[/TD]
[TD]Date[/TD]
[TD]Days[/TD]
[TD]reminder[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]New York[/TD]
[TD]8/30/18[/TD]
[TD]20[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]Boston[/TD]
[TD]8/17/18[/TD]
[TD]10[/TD]
[TD]Send Reminder[/TD]
[/TR]
</tbody>[/TABLE]

Something like this? For A Gentle Reminder: Boston, New York

Sub example()


Dim rownum As Long
Dim mystr As String


rownum = 2
Do Until Cells(rownum, 2).Value = ""
If Cells(rownum, 2).Value <= Date + 14 Then
mystr = mystr + Cells(rownum, 1).Value & ", "
End If
rownum = rownum + 1
Loop


mystr = Left(mystr, Len(mystr) - 2)


With OutLookMailItem
.to = MailDest1
.bcc = MailDest2
.Subject = Range("a1").Value & " " & mystr
.Body = Range("B1").Value




End Sub

 

[mrshl9898]

You're welcome!

So you want to send a list of the cities that are within 2 weeks AND have "send reminder" in column C?

Sub example()

Dim rownum As Long
Dim mystr As String

rownum = 2
Do Until Cells(rownum, 2).Value = ""
If Cells(rownum, 2).Value <= Date + 14 and Cells(rownum, 3).Value = "Send Reminder" Then                 ''change made here
mystr = mystr + Cells(rownum, 1).Value & ", "
End If
rownum = rownum + 1
Loop

mystr = Left(mystr, Len(mystr) - 2)

With OutLookMailItem
.to = MailDest1
.bcc = MailDest2
.Subject = Range("a1").Value & " " & mystr
.Body = Range("B1").Value

End Sub

 

[Mindy2017]

We are getting there...I can't say thank you enough. I do have what I hope is the last question...

the attached picture is showing that the line "mystr=left(mystr, Len(mystr)-2) is an invalid procedure or argument.

https://greyco.box.com/s/7s9bszb82054yd4fa3xr912gl7s7249t

 

[mrshl9898]

Not sure why that would be. It's only function is to remove the ", " though. If this is just for you maybe just ignore it.