Solved Selecting specific emails to email through vba

June 21, 2018 at 08:11:09
Specs: Windows 10
hi,

I have a table of contacts for people consisting of their names, number and emails etc..

I have already worked out how to send emails through a vba (button) but i want to know how it can automatically select emails from the table to send to if they have the word "Enquired" next to them.

for example if "Enquired" was next to abc@abc.com and also 123@123.com but not 321@321.com I would like the code to use just those emails to send emails to. ( this need to be automatic otherwise using the code to send emails then having to alter the code every time there was a different selection of recipients would be pointless).


And just to be awkward; the table does have an end, so I do not want the code searching through the whole column in the sheet but at the same time it needs to be dynamic as I constantly add and subtract rows from this table.


the start of the code looks something like this
olMail.to =

Thanks in advance to whomever is up for the challenge.


See More: Selecting specific emails to email through vba

Report •

✔ Best Answer
June 22, 2018 at 09:30:00
Try the code below.

I started with a Table (an actual Table, created via Insert...Table) Excel named the table Table1, so that is what I used in the code. I can add or delete rows and columns within Table1 and the code will always reference the entire Table.

In essence, when you create a Table in that manner, Excel creates a Dynamic Named Range for the Table. It also (in essence) creates Named Ranges for each Column so that you can refer to them directly: Table_Name[Column_Name]. Internally, I'm sure it's more complicated than just a Dynamic Named Range, but it acts very similar, allowing for the insertion and deletion of Rows and Columns without messing up the range references.

My table looks like this:

              A                       B
1       Enquire Column	         Email Column
2                                abc2@abc.com
3          Enquired              abc3@abc.com
4        yes Enquired            abc4@abc.com
5                                abc5@abc.com
6                                abc6@abc.com
7        Enquired Yes            abc7@abc.com

Based on this table, I would expect to see an email created for abc3, abc4 and abc7. (I do!)

In the code I used the Find and FindNext methods to locate each occurrence of Enquired in the Enquire Column column.

As far as the fact that your code only created 1 email, that was solved by moving the creation of the MailItem object to the inside of the search snippet and then setting it to Nothing before the next search begins. If you don't do that, you are using the same Object (the same email) over and over again each time. You need to create a new Object to get a new email.

Let me know if this works for you.

Sub SendEmail_V2()
'
' SendEmail Macro
'
Dim olMail As Object
Dim olApp As Object
Set olApp = CreateObject("Outlook.Application")

'Search "Enquire Column"

  With Range("Table1[Enquire Column]")
     Set e = .Find("enquired", Lookat:=xlPart)
       
'Send email when "enquired" found
       If Not e Is Nothing Then
         firstAddress = e.Address
           Do
             Set olMail = olApp.CreateItem(olMailItem)
               olMail.BCC = e.Offset(0, 1)
               olMail.Subject = Sheet8.Range("F2") + "Enquiry," + Sheet8.Range("F3")
               olMail.Body = "Hi,"
               olMail.Importance = olImportanceHigh
               olMail.ReadReceiptRequested = True
               olMail.Display
             Set olMail = Nothing
             
'Find Next "enquire"
             Set e = .FindNext(e)
               If e Is Nothing Then
                 GoTo DoneFinding
               End If
          Loop While e.Address <> firstAddress
       End If

DoneFinding:
  End With
 
'Clean up Object
             Set olApp = Nothing
  
End Sub

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code

message edited by DerbyDad03



#1
June 21, 2018 at 08:57:22
Assuming Excel, simply walk through the rows of the table looking for "enquired". Something like this:

    'example columns; edit to fit
    Const emlCol = 2
    Const enqCol = 3
    Dim L0, emailAddr
    For L0 = 1 To Cells.SpecialCells(xlCellTypeLastCell).Row
        If LCase$(Cells(L0, enqCol).Value) = "enquired" Then
            emailAddr = Cells(L0, emlCol).Value
            'further code to handle the actual email goes here
        End If
    Next L0

Note that "emlCol" and "enqCol" would have to be defined by you, and if you change the layout of the table, they'd have to be adjusted. Note also that this doesn't limit itself to just your table, but searches the entire spreadsheet. (Limiting to your table would take some code to decide where your table begins and ends, and that is entirely dependent on your data and what's on the rest of the sheet. Unless you have an enormous spreadsheet, or there is unrelated data elsewhere that also puts "enquired" in the same column, it isn't worth bothering with it.)

If you did your table in Word, it's a bit more involved (especially if you have multiple tables in the document) but the logic is similar:

    'more examples to edit as appropriate
    Const emlCol = 1
    Const enqCol = 4
    Dim ro As Row
    For Each ro In ActiveDocument.Tables(1).Rows
        ro.Cells(enqCol).Select
        If LCase$(Left$(Selection.Text, 8)) = "enquired" Then
            ro.Cells(emlCol).Select
            emailAddr = Left$(Selection.Text, Len(Selection.Text) - 2)
            'code to do emailing goes here
        End If
    Next ro

Note that there are other (likely better) ways to do this, but this way should work.

message edited by eriksiers


Report •

#2
June 22, 2018 at 00:48:49
Hi, eriksiers!

And thanks for getting back to me so quickly.

Unfortunately i do have the issue that there are more tables on the smae sheet of exactly the same layout undernaeth that i do not want to the code to apply to. So if i needed to limit the code search, how would i do that and where would i input the code.

so far, this is where I am up to:

Sub SendEmail()
'
' SendEmail Macro
'
'
Dim olApp As Outlook.Application
Set olApp = CreateObject("Outlook.Application")

Dim olMail As Outlook.MailItem
Set olMail = olApp.CreateItem(olMailItem)

olMail.BCC =
'example columns; edit to fit
Const emlCol = 2
Const enqCol = 3
Dim L0, emailAddr
For L0 = 1 To Cells.SpecialCells(xlCellTypeLastCell).Row
If LCase$(Cells(L0, enqCol).Value) = "enquired" Then
emailAddr = Cells(L0, emlCol).Value
olMail.Subject = sheet8.Range("F2") + "Enquiry," + sheet8.Range("F3")
olMail.Body = "Hi,"
olMail.Importance = olImportanceHigh
olMail.ReadReceiptRequested = True
olMail.Display

End Sub


at the moment the send or "bcc" is showing an error. I'm a complete noob to this.


So im sorry if youll be explaining some basics to me


Report •

#3
June 22, 2018 at 03:28:04
Hi,

so ive had a little shuffle around of the code and ive got it to work to some extent.

here is what I have in total:

Sub SendEmail()
'
' SendEmail Macro
'
'
Dim olApp As Outlook.Application
Set olApp = CreateObject("Outlook.Application")

Dim olMail As Outlook.MailItem
Set olMail = olApp.CreateItem(olMailItem)

Const emlCol = 2
Const enqCol = 1
Dim L0, emailAddr
For L0 = 1 To Cells.SpecialCells(xlCellTypeLastCell).Row
If LCase$(Cells(L0, enqCol).Value) = "enquired" Then
emailAddr = Cells(L0, emlCol).Value
olMail.BCC = emailAddr
olMail.Subject = sheet8.Range("F2") + "Enquiry," + sheet8.Range("F3")
olMail.Body = "Hi,"
olMail.Importance = olImportanceHigh
olMail.ReadReceiptRequested = True
olMail.Display
End If
Next L0

End Sub

But at the moment it only selects one of the options that have the works enquired next to there name. Would it be possible to select multiple items. and just a thought; I have worked on an if/lookup formula which extracts which emails have enquired next to them and puts them in a seperate table on a seperate sheet. would it be easier just to select all emails in that table? its a tough one.

Thanks so much for your help so far.


Report •

Related Solutions

#4
June 22, 2018 at 07:25:43
re: "And just to be awkward; the table does have an end, so I do not want the code searching through the whole column in the sheet but at the same time it needs to be dynamic as I constantly add and subtract rows from this table."

Have you considered either referring to the table by name or perhaps using a Dynamic Named Range? I think that either of these methods should allow you to refer to a specific, yet dynamic, "table".

See here for info on referring to a Table by name in VBA (or DAGS for other sites):

http://www.informit.com/articles/ar...

See here for info on Dynamic Named Ranges (or DAGS for other sites):

https://exceljet.net/formula/dynami...

re: "But at the moment it only selects one of the options that have the works enquired next to there name. Would it be possible to select multiple items."

*** Start Edit ***

I'm not quite sure what you mean by "only selects one of the options". Please explain that further.

I was able to duplicate the issue. See my next response for a solution.

*** End Edit ***

Have you considered using the FindNext method to search for "enquired" instead of looping through the table? That seems like it would be more efficient.

See here:

https://msdn.microsoft.com/en-us/vb...

Finally, just a posting tip:

Please click on the How-To link at the end of this post and read the instructions on how to post VBA code in this forum so that it is easier for us to read. Then edit/repost your code so that it looks similar to the example found via that link.

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code

message edited by DerbyDad03


Report •

#5
June 22, 2018 at 09:30:00
✔ Best Answer
Try the code below.

I started with a Table (an actual Table, created via Insert...Table) Excel named the table Table1, so that is what I used in the code. I can add or delete rows and columns within Table1 and the code will always reference the entire Table.

In essence, when you create a Table in that manner, Excel creates a Dynamic Named Range for the Table. It also (in essence) creates Named Ranges for each Column so that you can refer to them directly: Table_Name[Column_Name]. Internally, I'm sure it's more complicated than just a Dynamic Named Range, but it acts very similar, allowing for the insertion and deletion of Rows and Columns without messing up the range references.

My table looks like this:

              A                       B
1       Enquire Column	         Email Column
2                                abc2@abc.com
3          Enquired              abc3@abc.com
4        yes Enquired            abc4@abc.com
5                                abc5@abc.com
6                                abc6@abc.com
7        Enquired Yes            abc7@abc.com

Based on this table, I would expect to see an email created for abc3, abc4 and abc7. (I do!)

In the code I used the Find and FindNext methods to locate each occurrence of Enquired in the Enquire Column column.

As far as the fact that your code only created 1 email, that was solved by moving the creation of the MailItem object to the inside of the search snippet and then setting it to Nothing before the next search begins. If you don't do that, you are using the same Object (the same email) over and over again each time. You need to create a new Object to get a new email.

Let me know if this works for you.

Sub SendEmail_V2()
'
' SendEmail Macro
'
Dim olMail As Object
Dim olApp As Object
Set olApp = CreateObject("Outlook.Application")

'Search "Enquire Column"

  With Range("Table1[Enquire Column]")
     Set e = .Find("enquired", Lookat:=xlPart)
       
'Send email when "enquired" found
       If Not e Is Nothing Then
         firstAddress = e.Address
           Do
             Set olMail = olApp.CreateItem(olMailItem)
               olMail.BCC = e.Offset(0, 1)
               olMail.Subject = Sheet8.Range("F2") + "Enquiry," + Sheet8.Range("F3")
               olMail.Body = "Hi,"
               olMail.Importance = olImportanceHigh
               olMail.ReadReceiptRequested = True
               olMail.Display
             Set olMail = Nothing
             
'Find Next "enquire"
             Set e = .FindNext(e)
               If e Is Nothing Then
                 GoTo DoneFinding
               End If
          Loop While e.Address <> firstAddress
       End If

DoneFinding:
  End With
 
'Clean up Object
             Set olApp = Nothing
  
End Sub

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code

message edited by DerbyDad03


Report •

#6
June 25, 2018 at 01:32:29
Hi Derby dad!
Thank you so much for your response! it was very helpfull. your code works absolutely brilliantly! if you live in Derby I would love to buy you a drink as a way of saying thank you.
But first there is another query:

In an ideal world, I would like to drag and drop pdf and word files into a sheet of the excel workbook, then when the email macro is running, for it to search for the attachments in that sheet and attach them to the email. Could you tell me if this is possible at all? or would i need an alternative solution?.

Again, thank you both so much for your time and expertise.


Look forwards to hearing from you soon.


Report •

#7
June 25, 2018 at 04:11:22
If you are satisfied with the solution to the question asked in your subject line, please mark this thread as solved.

The requirement to search for and attach documents to an email is a totally different question and deserves its own thread with a relevant subject line. Feel free to reference this thread so that members will know that you want add that requirement to this code, but I think that your latest question will get more attention if it is in its own thread.

Thanks.

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code


Report •

Ask Question