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.)

a b 1 A Gentle Reminder:<strike></strike> <strike></strike> 2 Boston 8/15/2018 3 New York 8/20/2018 <tbody> </tbody>

<colgroup><col></colgroup><tbody>
</tbody>

 

[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

	A	B	C	D
1	Friendly Reminder
3	Location	Date	Days	reminder
4	New York	8/30/18	20
5	Boston	8/17/18	10	Send Reminder

<tbody>
</tbody>

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.