Using cell reference to get search string for .find in vba

Question

I am trying to write a code to find a mail by subject and work with that. When I am putting the exact subject line as string in myItems.Find, it works fine. But if I put the exact same thing in an excel sheet and try to run the code with cell reference its throwing error(Run-time error '-2147352567). Please help me out. Thanks in advance.

Sub SearchMail()
    Dim myOlApp As New Outlook.Application
    Dim myNameSpace As Outlook.Namespace
    Dim sentbox As Outlook.MAPIFolder
    Dim myItems As Outlook.Items
    Dim myItem As Object
    Dim Found As Boolean
    Dim mail As Outlook.MailItem
    Dim ForwardMail As Outlook.MailItem

    Set myNameSpace = myOlApp.GetNamespace("MAPI")
    Set sentbox = myNameSpace.GetDefaultFolder(olFolderSentMail)
    Set myItems = sentbox.Items

    'This Works!!
    'Set mail = myItems.Find("[Subject] = ""Exact Search text""")


    Workbooks("Outlook Mail.xlsm").Sheets("MailingList").Activate
    Sheets("MailingList").Select

    'This doesn't. Why??
    Set mail = myItems.Find("[Subject] = Cells(17,11).Value")

    Set ForwardMail = mail.ReplyAll
    ForwardMail.Display
End Sub

Zac · Accepted Answer · 2018-04-05 11:02:09Z

1

When you put it in quotes, it becomes a string. try this:

Set mail = myItems.Find("[Subject] = " & Cells(17,11).Value)

Although I would advice that you don't use just Cell. Late bind your sheet and then use it:

Set mail = myItems.Find("[Subject] = """ & ThisWorkbook.Worksheets("MailingList").Cells(17,11).Value) & """"

This way you don't have to select the sheet either

edited Apr 5, 2018 at 11:02

answered Apr 5, 2018 at 10:40

Zac

1,9501 gold badge10 silver badges23 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Anurup Dey Over a year ago

Hi @Zac , Trying this I'm getting Run-time error '91': Object variable or with block variable not set. What's going wrong?

Zac Over a year ago

Which statement are you using? first or the second? also, can you check the value in debug mode for the cell?

Zac Over a year ago

I think it was missing quotes. I've updated the second statement. Try that (sorry I can't test this)

Anurup Dey Over a year ago

Hi Zac, I used the second statement and it works now. Thank you so much for your valuable input.

Zac Over a year ago

No worries. Glad it helped

Collectives™ on Stack Overflow

Using cell reference to get search string for .find in vba

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related