• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Excel vba to secrch in out look

haribsha

New Member
Dear Experts

I am using below code to search for email in out look. It works good, but all serach results (ie Emails) are opening one by one and out look gets stuck (there will be lots of email containing given key word).

Can anybody pls help me to get a code to list all emails containing given key word ("City Pharmacy") in out look seach box only instead opening one by one.

>>> use code - tags <<<
Code:
Sub Test()
'this is working but all windows are opending one by one.
Dim olApp As Outlook.Application
Dim olNs As Namespace
Dim Fldr As MAPIFolder
Dim olMail As Variant
Dim i As Integer

Set olApp = New Outlook.Application
Set olNs = olApp.GetNamespace("MAPI")
Set Fldr = olNs.GetDefaultFolder(olFolderInbox)
i = 1

For Each olMail In Fldr.Items
If InStr(olMail.Subject, "City Pharmacy") <> 0 Then
olMail.Display

i = i + 1
End If
Next olMail
End Sub
 
Last edited by a moderator:
Step 1: Create a Sub Procedure by naming the macro.
Step 2: Define the variable as Outlook.
Step 3: We need to send an email in Outlook so define another variable as “Outlook.Mailitem”
Step 4: In the previous steps we have defined the variable now we need to set them.
Step 5: Now set the Second Variable “Outlook Mail”
Step 6: We can now use the VBA Outlook using the “With”
Step 7: Inside the “With” statement, we can see a list by putting a dot which is known as “Intellisense List”.
Step 8: First select the body format as olFormatHtml
Step 9: Select “.Display” to display the mail
Step 10: Select “.HTMLbody” to write the email
Step 11: Now we need to add the receiver of the email. For this, you need to use “.To”.
Step 12: If you want to add someone in “CC” & “BCC”, you can use “.CC” and “.BCC”
Step 13: To add a subject for the email that we are sending, we can use “.Subject”
Step 14: We can add our current workbook as an attachment in the email with the help of “.Attachment” Property. To do that first declare a variable Source as a string.
Step 15: Now the last code is to finally send the email for which we can use “.send”. But make sure to close the With and Sub procedure by “End with” and “End Sub”
Step 16: Run the code by hitting F5 or Run button and see the output.

Hope You Find This Useful,
Peter
 
Back
Top