You're the smartest person I've ever met on TH-cam, congratulations and thank you very much for all the help you offer with your channel, thanks to you I'm managing to solve many problems that are specific to my work, I don't know of another channel with such vast content as its about Outlook with VBA, i'm your fan!
Hi Andrew, I am getting stuck assigning variables in the DASL string instead of hardcoding. @ 14:29 instead of looking for %microsoft%, I want to use a value from the worksheet, store it in a variable and use it like %variable&? Could you please reply?
Hi John! Remove the word microsoft and replace it with these characters: " & MyVariableName & " Or, if you just want to refer to the value of a range without using a variable: " & Range("A1").Value & "
@@WiseOwlTutorials Thank you Andrew. I will try it and revert. As a matter of fact, I did try it putting "&" around. It din't work. This string that you showed to copy from Outlook Filter dialog box is nasty. I am sure I am messing up with all the single and double quotes. By the way, here's my string: filterstring = "@SQL=((""schemas.microsoft.com/mapi/proptag/0x0037001f"" LIKE '%Completed:%'" & _ " AND ""schemas.microsoft.com/mapi/proptag/0x0042001f"" LIKE '%Alex Garcia%')" & _ " AND ""urn:schemas:httpmail:read"" = 0)" Where the subject contains "Completed:" and SenderName is "Alex Garcia". I tried every possible combination to replace those two values with a variable. I put ampersands, single quotes around ampersands, double quotes, without quotes, and finally gave up.
filterString = "@SQL=((""schemas.microsoft.com/mapi/proptag/0x0037001f"" LIKE '%" & StatusVariable & "%'" & _ " AND ""schemas.microsoft.com/mapi/proptag/0x0042001f"" LIKE '%" & NameVariable & "%')" & _ " AND ""urn:schemas:httpmail:read"" = 0)" It's fiddly but having a sequence to follow helps. I find that this works for me when I need to replace a hardcoded value(for example Alex Garcia in your example). '%Alex Garcia%' 1) Select and delete Alex Garcia '%%' 2) Type in two double quote characters '%""%' 3) Move the cursor back one space between the two double quote characters and type in two spaces '%" "%' 4) Move the cursor back one space between the two space characters and type in two ampersands '%" && "%' 5) Move the cursor back one space between the two ampersands and type in two spaces '%" & & "%' 6) Move the cursor back one space between the spaces and insert your variable name '%" & NameVariable & "%' I hope that helps!
Hello - an excellent video, like all of the videos in your series. I have a question. Is there a way to transfer the results of a DASL filter to the Outlook Window? In the example code in this video, the DASL filter results are displayed in the Visual Basic Immediate window. What I am trying to do is display in Outlook the filtered set of e-mails resulting from the fol.Items.Restrict(filterString) action (in other words display the results from the VBA code in Outlook as if a search by "Subject Line" had been manually executed in Outlook itself). I have a macro button in an Excel Spreadsheet that when clicked initiates an Outlook Search by "Subject", utilizing a concatenated text string consisting of the phrase "RFP No." followed by the number value from Column A in the ActiveRow of the Spreadsheet (for example "RFP No. 8"). When using this text string as the Search string in the "Application.ActiveExplorer.Search" function in VBA, it does display the results in Outlook, but the results include not only the e-mails with subject line including the exact full text string phrase (for example "RFP No. 8), but also e-mails that include "RFP", "No.", or just the number itself (for example"8") as part of the subject line. IN other words the search results includes partial phrase matches. So it seems I need the "advanced search" capabilities of a DASL filter to be passed to Outlook so that only e-mails including the entire text string phrase as part of their subject line are displayed. Thanks very much. Jim
Hi Andrew, I don’t have any special word how can I thank you I have been searching this for a while, thank you soooooo much for this awesome video tutorial, I have question though if a subject line has text with apostrophe mark like “hi this mail’s is important” like that as u can see that this subject line has apostrophe, can dsl filter will search also. Thanks Kashif
Hi Rafael, as far as I know there isn't a native method in Outlook to search within an attachment. I think the only way to do this would be to open the attachment in the relevant application and then search within that application. There's an example of how to do this if the attachment is an Excel, Word or PDF document in the accepted answer here stackoverflow.com/questions/40267587/excel-vba-for-searching-string-within-an-outlook-attachment-flagging-email-if-m And another example of how to do the same thing if the attachment is another email message here (the accepted answer is near the bottom) www.ozgrid.com/forum/index.php?thread/115809-vba-code-to-search-for-text-in-mail-attahcment-in-outlook/ I hope that helps!
Hi Sayan, you can concatenate your variable into a string just as for any standard concatenation in VBA: Dim MyQueryString as String Dim MyInput as string MyInput = InputBox("Type something") MyQueryString= "Start of your query" & MyInput & "rest of your query" Debug.Print MyQueryString
Hi Andy. Very good tutorial. I am facing a situation where I have to create send Outlook events, and if the source data has a meeting that is already created and the data is updated. The macro should automatically update the changes and send update. Here I am currently identifying the meeting item by restricting subject and start date. But wanted to know is there a way to find the meeting item using the global appointment ID. This will be captured and saved on a separate sheet once a new meeting is created along with other details. Just was wondering is there's a method or a way to restrict global appointment ID. Thanks
Hi! I'm not entirely sure - this seems to suggest that you can get at the global appointment id using DASL syntax www.office-forums.com/threads/global-appointment-id.2164008/ It also seems to suggest that you'll need a separate add-in called Redemption to access it. Sorry I don't know the full answer but maybe that can point you in the right direction!
With many thanks for your helpful tutorials, they are perfect! I am wondering does this tutor have any learning course available on websites so that people can buy a complete collection?!
You are very welcome! We don't currently have an online tutorial but we have been working on one and a basic version should be appearing early in the new year - watch this space!
Thanks Andy. Having not researched the meaning of DASL yet, I was curious now... what does DASL stand for? I thought you were going to define the acronym any minute, but I was incorrect. LOL.
Ahh I'm sorry to disappoint! Straight from the horse's mouth as it were: "DAV Searching and Locating (DASL) query language is based on the Microsoft Exchange Server implementation of DASL in Outlook. DASL has been used in several versions of Outlook. In Outlook 2007, DASL has new prominence because it can be used to return results in the new Table object. DAV is an abbreviated version of Web Distributed Authoring and Versioning (WebDAV)." docs.microsoft.com/en-us/previous-versions/office/developer/office-2007/cc513841(v=office.12)#SearchingOutlookData_Overview I had looked it up before creating the video but thought it was so unimportant that I decided not to include it, sorry about that!
dear, how to query mail sender "Mail Delivery Subsystem" Or Subject "undeliverable: xxxxx" ? after send email but system return mail delivery => how to check or not ?
Hi Praveen, you can see in this video how to add filters using the Subject field. You can use NOT LIKE to exclude items containing a specific word. I hope it helps!
While automating outlook through excel. There are some warning messages and pop-ups. Is there a way to bypass these or handle them? Example when sending a message without a subject, or when a meeting's time is changed there are pop-ups. Please help am stuck with this issue.
Here's a suggestion for the No Subject warning www.slipstick.com/developer/vba-disable-outlook-2010-no-subject-warning/ I'm not sure if there's a blanket way to suppress all popups
@@WiseOwlTutorials hey Andy, thanks for your response, I figured out this using windows api. Just a suggestion if you could make tutorials on using windows api in VBA would be a great help to others and a good addition to the channel as well, there are not much covered on this topic, if anyone has to find there are a few videos which are quite old and doesn't really show up in TH-cam search.
@@ganeshs1360 Yeah we have a few VBA videos which use Windows API functions (such as DownloadFromURL) but it's a tricky topic to make videos on - there are so many functions. The most I've done with Windows API functions in VBA is recreating Flappy Bird in Excel - you can see the various articles and descriptions of various functions here www.wiseowl.co.uk/blog/s398/flappy-bird-excel-vba-index.htm
Hi, these tutorials have helped me greatly in my project! One question, is it possible to use the DASL filter to filter out emails based on user input fields? E.g. user inputs keywords found in the subject and DASL filter is able to filter out these emails? Many thanks!
Hi Darylene! Sure, in VBA the filter is simply a string. You can concatenate the filter string from as many different elements as you like, included values entered by the user. You can use Excel ranges or VBA input boxes to capture the user input. I hope that helps!
@@WiseOwlTutorials I see, hmm I tried the below code but it did not work. I would like to search emails with subject titles containing specific names. Not sure if the % should be a different symbol such that the FilterString is able to read FilterName? FilterName = InputBox("Enter Name") FilterString = "@SQL=""urn:schemas:httpmail:subject"" LIKE '%FilterName%'" Thanks so much for your help!
@@darylenetay3441 Hi Darylene! You're almost there, you just need to concatenate the FilterName variable into the string rather than include it literally. At the moment, this code: FilterString = "@SQL=""urn:schemas:httpmail:subject"" LIKE '%FilterName%'" Is searching for subjects which literally contain the text FilterName. This code: FilterString = "@SQL=""urn:schemas:httpmail:subject"" LIKE '%" & FilterName & "%'" Will search for subjects containing whatever your user enters into the inputbox. I hope it helps!
Great video Andrew. I am glad I found your tutorials. I've been learning quite a lot, thanks. Suppose I've a custom ribbon tab with buttons that run some macros, and one of the buttons runs a macro that apply one of these DASL filters. How can I show the applied filter in the current view?
Hi Elio! Happy to hear that you've been enjoying the videos. I'm sorry but I don't know of a good way to display the filter in an Outlook view. The StatusBar is one option that you could try docs.microsoft.com/en-us/office/vba/api/project.application.statusbar
@@WiseOwlTutorials Andrew, thanks for the answer. I tried to use DASL filters with calendar items instead of mail items, using this FilterString = "@SQL=%lastweek(""urn:schemas:calendar:dtstart"")%" but I am getting unsorted and old appointments! Can you give me an hint?
@@efernandes67 Hi Elio! I'm not sure, perhaps it would be easier to calculate the start date and create a filter like this MyDate = DateAdd("d", -7, Date)
filterString = _ "@SQL=""urn:schemas:calendar:dtstart"" >= '" & _ Format(MyDate, "dd mmm yyyy hh:mm") & "'" So that will subtract 7 days from the current date and create a filter for calendar items whose start date is on or after that date. I hope it helps!
Hi Andrew, Thank You so much for your valuable time. i need your help on the below scenario set fol = ns.GetDefaultFolder(olFolderInbox).Folders("PROJECT") PROJECT is the subfolder name I have mentioned as per my convenient. My requriment is set fol = ns.GetDefaultFolder(olFolderInbox).Folders & sheet1.Range("A1").value in A1 = PROJECT Is there any possible way to provide user for their own Subfolder name under Inbox
Hi Andrew, Thank you so much for this tutorial, I need you help regarding a vba code for outlook mail. Problem:- I need to loop each mail in the Inbox where Category is blank, then read that mails, email address and then search that email address in the sql server data base, and whatever the rows (will return outlook folder names) returns, that mail will go to that folders, the problem is that, because I am looping in a "Inbox" folder where new mail comes in every second and if the new mail comes in between the loop some emails is not picking by the loop, and also the main concern is that the vba code is taking lot of time to run because the "Inbox" folder has around 4000 mails and it is growing by each data. Sample vba code:- Sub MoveMail_Example() Dim objOutlook As Outlook.Application Dim objNameSpace As Outlook.Namespace Dim objFolSource As Outlook.Folder, objFolDes_1 As Outlook.Folder, objFolDes_2 As Outlook.Folder Dim ObjItem As Object, objItem2 As Object Dim objCategoryMails As Outlook.Items Dim lLoop As Long, lCount As Long Dim sFilterString As String
Set objOutlook = New Outlook.Application Set objNameSpace = objOutlook.GetNamespace("MAPI") Set objFolSource = objNameSpace.Folders("abc@somecompany.com").Folders("SourceFolder") Set objFolDes_1 = objNameSpace.Folders("abc@somecompany.com").Folders("DestinationFolder_1") Set objFolDes_2 = objNameSpace.Folders("abc@somecompany.com").Folders("DestinationFolder_2")
Set objCategoryMails = objFolSource.Items.Restrict(sFilterString) lCount = objCategoryMails.Count
'Start 1st Method not working, - if new mail comes in between the loop in the source folder (Inbox) 'Then below code left some mails, and not looping to the desired mail If lCount > 0 Then For Each ObjItem In objCategoryMails Set objItem2 = ObjItem.Copy objItem2.Move objFolDes_1
Set objItem2 = ObjItem.Copy objItem2.Move objFolDes_2
ObjItem.Delete Next ObjItem End If 'End ====
'Start 2nd Method not working, - if new mail comes in between the loop in the source folder (Inbox) 'The Index of the looping mail changed and generating the error message when trying to delete the looped mail, after copy and move. If lCount > 0 Then For lLoop = lCount To 1 Step -1 Set ObjItem = objCategoryMails(lLoop)
Set objItem2 = ObjItem.Copy objItem2.Move objFolDes_1
Set objItem2 = ObjItem.Copy objItem2.Move objFolDes_2
ObjItem.Delete Next lLoop End If 'End ====
'Start 3rd Method not working, - if new mail comes in between the loop in the source folder (Inbox) 'Then below code is not looping some mails, that should have included in the loop For Each ObjItem In objFolSource.Items If ObjItem.Categories = vbNullString And ObjItem.Class = olMail Then Set objItem2 = ObjItem.Copy objItem2.Move objFolDes_1
Set objItem2 = ObjItem.Copy objItem2.Move objFolDes_2
ObjItem.Delete End If Next ObjItem 'End ====
End Sub Thanks in advance for reading this mail and for your help. As always God bless you. Thanks Kashif
Hi Kashif! I don't have a lot of experience with Outlook but I wonder if it might be better to do this task using either the NewMail event or the ItemAdd event? docs.microsoft.com/en-us/office/vba/api/outlook.application.newmail docs.microsoft.com/en-us/office/vba/api/outlook.items.itemadd These events are triggered automatically when either a new mail item is received or an item is added to a folder.
Hi Andrew, thanks for the reply, ok so in between the for each loop how can we trigger an event and how can that new email will add automatically in running for each loop? Thanks Kashif
@@kashifkhanspecial Hi Kashif, you won't need to trigger the event - it will be triggered automatically when the event happens. The information on the pages I linked to earlier describe how to use a class module to initialise the event handler for these events. The ItemAdd event looks like the best one to use because it has a parameter called Item which provides you with access to the item that has just been added to the folder. So you don't need to loop over all of the items each time - your code will deal with each item individually. You can set the ItemAdd event to be triggered on the Inbox folder. I haven't done this myself so I can't guide you any more than that.
Hi Andrew, Thank you so much for the reply, I will try my level best to implement your suggestion. Thanks again, take care and have a nice and healthy day ahead. Thanks Kashif
Hi Thank you so much for the information. I have a requirement user has to provide the serching key words. When I am trying to give like this i am getting error as " Cannot parse conditions. Error at "@SQL="urn:schemas:httpmail:textdescript..... I have mentioned as Filterstring = "@SQL=""urn: schemas: httpmail:textdescription" & Sheet1.Range("A1").value User input: In A1 = "Like '%Hi Lyndy%'" If you can suggest me it would be very helpful
Hi Prasanth, you need to include the closing quotes for the textdescription property and a space to follow it. Something like this: Filterstring = "@SQL=""urn: schemas: httpmail:textdescription"" " & Sheet1.Range("A1").value I hope it helps!
@@WiseOwlTutorials Thank you so much For your Quick Response. Yes problem resolved i missed the space ☺️ in SQL Query. Thanks a lot. It helped me a lot
You're the smartest person I've ever met on TH-cam, congratulations and thank you very much for all the help you offer with your channel, thanks to you I'm managing to solve many problems that are specific to my work, I don't know of another channel with such vast content as its about Outlook with VBA, i'm your fan!
Delighted to hear that the videos have helped you, thank you for the kind comments and for watching!
One of the most helpful videos I have ever watched. Have been trying to figure out much of this for a long time. THANK YOU
Thanks Dane! I'm really happy to hear that you found it useful and thank you for taking the time to leave a comment!
I can’t wait to try some of this code tomorrow!!!
@@ksoonsoon 😀 Have fun!
@@WiseOwlTutorials my pleasure. Credit where credit is due, and that was some outstanding stuff. Thanks again
Hello,
I would like to thank you very much as i was able to finish my project thanks to your video
Happy to hear that the video helped, thank you for watching!
Great video, sir!
Thank you!
Can anyone tell me please, how to OPEN the excel attachment in this case??? I can’t figure it out…((((
Hi Andrew, I am getting stuck assigning variables in the DASL string instead of hardcoding. @ 14:29 instead of looking for %microsoft%, I want to use a value from the worksheet, store it in a variable and use it like %variable&? Could you please reply?
Hi John! Remove the word microsoft and replace it with these characters:
" & MyVariableName & "
Or, if you just want to refer to the value of a range without using a variable:
" & Range("A1").Value & "
@@WiseOwlTutorials Thank you Andrew. I will try it and revert. As a matter of fact, I did try it putting "&" around. It din't work. This string that you showed to copy from Outlook Filter dialog box is nasty. I am sure I am messing up with all the single and double quotes. By the way, here's my string:
filterstring = "@SQL=((""schemas.microsoft.com/mapi/proptag/0x0037001f"" LIKE '%Completed:%'" & _
" AND ""schemas.microsoft.com/mapi/proptag/0x0042001f"" LIKE '%Alex Garcia%')" & _
" AND ""urn:schemas:httpmail:read"" = 0)"
Where the subject contains "Completed:" and SenderName is "Alex Garcia". I tried every possible combination to replace those two values with a variable. I put ampersands, single quotes around ampersands, double quotes, without quotes, and finally gave up.
@@johnabram4159 Hi John, that certainly is nasty! Here's a basic pattern for you:
Dim StatusVariable As String
Dim NameVariable As String
StatusVariable = Range("A1").Value
NameVariable = Range("A2").Value
filterString = "@SQL=((""schemas.microsoft.com/mapi/proptag/0x0037001f"" LIKE '%" & StatusVariable & "%'" & _
" AND ""schemas.microsoft.com/mapi/proptag/0x0042001f"" LIKE '%" & NameVariable & "%')" & _
" AND ""urn:schemas:httpmail:read"" = 0)"
It's fiddly but having a sequence to follow helps. I find that this works for me when I need to replace a hardcoded value(for example Alex Garcia in your example).
'%Alex Garcia%'
1) Select and delete Alex Garcia
'%%'
2) Type in two double quote characters
'%""%'
3) Move the cursor back one space between the two double quote characters and type in two spaces
'%" "%'
4) Move the cursor back one space between the two space characters and type in two ampersands
'%" && "%'
5) Move the cursor back one space between the two ampersands and type in two spaces
'%" & & "%'
6) Move the cursor back one space between the spaces and insert your variable name
'%" & NameVariable & "%'
I hope that helps!
Hello - an excellent video, like all of the videos in your series. I have a question. Is there a way to transfer the results of a DASL filter to the Outlook Window? In the example code in this video, the DASL filter results are displayed in the Visual Basic Immediate window. What I am trying to do is display in Outlook the filtered set of e-mails resulting from the fol.Items.Restrict(filterString) action (in other words display the results from the VBA code in Outlook as if a search by "Subject Line" had been manually executed in Outlook itself). I have a macro button in an Excel Spreadsheet that when clicked initiates an Outlook Search by "Subject", utilizing a concatenated text string consisting of the phrase "RFP No." followed by the number value from Column A in the ActiveRow of the Spreadsheet (for example "RFP No. 8"). When using this text string as the Search string in the "Application.ActiveExplorer.Search" function in VBA, it does display the results in Outlook, but the results include not only the e-mails with subject line including the exact full text string phrase (for example "RFP No. 8), but also e-mails that include "RFP", "No.", or just the number itself (for example"8") as part of the subject line. IN other words the search results includes partial phrase matches. So it seems I need the "advanced search" capabilities of a DASL filter to be passed to Outlook so that only e-mails including the entire text string phrase as part of their subject line are displayed. Thanks very much. Jim
Hi Andrew,
I don’t have any special word how can I thank you I have been searching this for a while, thank you soooooo much for this awesome video tutorial, I have question though if a subject line has text with apostrophe mark like “hi this mail’s is important” like that as u can see that this subject line has apostrophe, can dsl filter will search also.
Thanks
Kashif
Hi Andrew, is there any way to search inside the attachments? Thanks for the tutorial!
Hi Rafael, as far as I know there isn't a native method in Outlook to search within an attachment. I think the only way to do this would be to open the attachment in the relevant application and then search within that application. There's an example of how to do this if the attachment is an Excel, Word or PDF document in the accepted answer here stackoverflow.com/questions/40267587/excel-vba-for-searching-string-within-an-outlook-attachment-flagging-email-if-m
And another example of how to do the same thing if the attachment is another email message here (the accepted answer is near the bottom) www.ozgrid.com/forum/index.php?thread/115809-vba-code-to-search-for-text-in-mail-attahcment-in-outlook/
I hope that helps!
@@WiseOwlTutorials thank you so much
Hi very helpful tutorial, could you please help. I want to skip the mail which contains recall subject
Please advise if the subject string is an user input and saved in a variable then how to pass the variable while constructing the filter string.
Hi Sayan, you can concatenate your variable into a string just as for any standard concatenation in VBA:
Dim MyQueryString as String
Dim MyInput as string
MyInput = InputBox("Type something")
MyQueryString= "Start of your query" & MyInput & "rest of your query"
Debug.Print MyQueryString
Hi Andy. Very good tutorial. I am facing a situation where I have to create send Outlook events, and if the source data has a meeting that is already created and the data is updated. The macro should automatically update the changes and send update. Here I am currently identifying the meeting item by restricting subject and start date. But wanted to know is there a way to find the meeting item using the global appointment ID. This will be captured and saved on a separate sheet once a new meeting is created along with other details. Just was wondering is there's a method or a way to restrict global appointment ID. Thanks
Hi! I'm not entirely sure - this seems to suggest that you can get at the global appointment id using DASL syntax
www.office-forums.com/threads/global-appointment-id.2164008/
It also seems to suggest that you'll need a separate add-in called Redemption to access it. Sorry I don't know the full answer but maybe that can point you in the right direction!
@@WiseOwlTutorials Hey thanks Andy for your response, am afraid that we are not allowed to use any 3rd party plugins or add-ins with the automation.
With many thanks for your helpful tutorials, they are perfect!
I am wondering does this tutor have any learning course available on websites so that people can buy a complete collection?!
You are very welcome! We don't currently have an online tutorial but we have been working on one and a basic version should be appearing early in the new year - watch this space!
Thanks Andy. Having not researched the meaning of DASL yet, I was curious now... what does DASL stand for? I thought you were going to define the acronym any minute, but I was incorrect. LOL.
Ahh I'm sorry to disappoint! Straight from the horse's mouth as it were: "DAV Searching and Locating (DASL) query language is based on the Microsoft Exchange Server implementation of DASL in Outlook. DASL has been used in several versions of Outlook. In Outlook 2007, DASL has new prominence because it can be used to return results in the new Table object. DAV is an abbreviated version of Web Distributed Authoring and Versioning (WebDAV)." docs.microsoft.com/en-us/previous-versions/office/developer/office-2007/cc513841(v=office.12)#SearchingOutlookData_Overview
I had looked it up before creating the video but thought it was so unimportant that I decided not to include it, sorry about that!
dear, how to query mail sender "Mail Delivery Subsystem" Or Subject "undeliverable: xxxxx" ? after send email but system return mail delivery => how to check or not ?
Hi could you please help me how skip mail which subject contains recall and goto next Outlook mail.i am looking forward
Hi Praveen, you can see in this video how to add filters using the Subject field. You can use NOT LIKE to exclude items containing a specific word. I hope it helps!
While automating outlook through excel. There are some warning messages and pop-ups. Is there a way to bypass these or handle them? Example when sending a message without a subject, or when a meeting's time is changed there are pop-ups. Please help am stuck with this issue.
Here's a suggestion for the No Subject warning www.slipstick.com/developer/vba-disable-outlook-2010-no-subject-warning/
I'm not sure if there's a blanket way to suppress all popups
@@WiseOwlTutorials hey Andy, thanks for your response, I figured out this using windows api. Just a suggestion if you could make tutorials on using windows api in VBA would be a great help to others and a good addition to the channel as well, there are not much covered on this topic, if anyone has to find there are a few videos which are quite old and doesn't really show up in TH-cam search.
@@ganeshs1360 Yeah we have a few VBA videos which use Windows API functions (such as DownloadFromURL) but it's a tricky topic to make videos on - there are so many functions. The most I've done with Windows API functions in VBA is recreating Flappy Bird in Excel - you can see the various articles and descriptions of various functions here www.wiseowl.co.uk/blog/s398/flappy-bird-excel-vba-index.htm
Hi, these tutorials have helped me greatly in my project!
One question, is it possible to use the DASL filter to filter out emails based on user input fields? E.g. user inputs keywords found in the subject and DASL filter is able to filter out these emails? Many thanks!
Hi Darylene! Sure, in VBA the filter is simply a string. You can concatenate the filter string from as many different elements as you like, included values entered by the user. You can use Excel ranges or VBA input boxes to capture the user input. I hope that helps!
@@WiseOwlTutorials I see, hmm I tried the below code but it did not work. I would like to search emails with subject titles containing specific names. Not sure if the % should be a different symbol such that the FilterString is able to read FilterName?
FilterName = InputBox("Enter Name")
FilterString = "@SQL=""urn:schemas:httpmail:subject"" LIKE '%FilterName%'"
Thanks so much for your help!
@@darylenetay3441 Hi Darylene! You're almost there, you just need to concatenate the FilterName variable into the string rather than include it literally. At the moment, this code:
FilterString = "@SQL=""urn:schemas:httpmail:subject"" LIKE '%FilterName%'"
Is searching for subjects which literally contain the text FilterName. This code:
FilterString = "@SQL=""urn:schemas:httpmail:subject"" LIKE '%" & FilterName & "%'"
Will search for subjects containing whatever your user enters into the inputbox.
I hope it helps!
@@WiseOwlTutorials This is perfect!! Thank you so much! 👍
@@darylenetay3441 No problem!
Great video Andrew. I am glad I found your tutorials. I've been learning quite a lot, thanks.
Suppose I've a custom ribbon tab with buttons that run some macros, and one of the buttons runs a macro that apply one of these DASL filters. How can I show the applied filter in the current view?
Hi Elio! Happy to hear that you've been enjoying the videos. I'm sorry but I don't know of a good way to display the filter in an Outlook view. The StatusBar is one option that you could try docs.microsoft.com/en-us/office/vba/api/project.application.statusbar
@@WiseOwlTutorials Andrew, thanks for the answer.
I tried to use DASL filters with calendar items instead of mail items, using this FilterString = "@SQL=%lastweek(""urn:schemas:calendar:dtstart"")%" but I am getting unsorted and old appointments! Can you give me an hint?
@@efernandes67 Hi Elio! I'm not sure, perhaps it would be easier to calculate the start date and create a filter like this
MyDate = DateAdd("d", -7, Date)
filterString = _
"@SQL=""urn:schemas:calendar:dtstart"" >= '" & _
Format(MyDate, "dd mmm yyyy hh:mm") & "'"
So that will subtract 7 days from the current date and create a filter for calendar items whose start date is on or after that date.
I hope it helps!
@@WiseOwlTutorials Andrew, it helped. Filtering calendar items it's not as simple as filtering email items due to recurring items! Thanks.
@@efernandes67 Excellent! Happy to hear that you made progress, thanks for letting me know!
Hi Andrew, Thank You so much for your valuable time.
i need your help on the below scenario
set fol = ns.GetDefaultFolder(olFolderInbox).Folders("PROJECT")
PROJECT is the subfolder name I have mentioned as per my convenient.
My requriment is
set fol = ns.GetDefaultFolder(olFolderInbox).Folders & sheet1.Range("A1").value
in A1 = PROJECT
Is there any possible way to provide user for their own Subfolder name under Inbox
Hi Prasanth. Instead of this:
.Folders("PROJECT")
Do this:
.Folders(sheet1.Range("A1").value)
@@WiseOwlTutorials
Thank you 😊❤️
Hi Andrew,
Thank you so much for this tutorial, I need you help regarding a vba code for outlook mail.
Problem:- I need to loop each mail in the Inbox where Category is blank, then read that mails, email address and then search that email address in the sql server data base, and whatever the rows (will return outlook folder names) returns, that mail will go to that folders, the problem is that, because I am looping in a "Inbox" folder where new mail comes in every second and if the new mail comes in between the loop some emails is not picking by the loop, and also the main concern is that the vba code is taking lot of time to run because the "Inbox" folder has around 4000 mails and it is growing by each data.
Sample vba code:-
Sub MoveMail_Example()
Dim objOutlook As Outlook.Application
Dim objNameSpace As Outlook.Namespace
Dim objFolSource As Outlook.Folder, objFolDes_1 As Outlook.Folder, objFolDes_2 As Outlook.Folder
Dim ObjItem As Object, objItem2 As Object
Dim objCategoryMails As Outlook.Items
Dim lLoop As Long, lCount As Long
Dim sFilterString As String
Set objOutlook = New Outlook.Application
Set objNameSpace = objOutlook.GetNamespace("MAPI")
Set objFolSource = objNameSpace.Folders("abc@somecompany.com").Folders("SourceFolder")
Set objFolDes_1 = objNameSpace.Folders("abc@somecompany.com").Folders("DestinationFolder_1")
Set objFolDes_2 = objNameSpace.Folders("abc@somecompany.com").Folders("DestinationFolder_2")
sFilterString = "@SQL=" & Chr(34) & "urn:schemas-microsoft-com:office:office#Keywords" & Chr(34) & " is null"
Set objCategoryMails = objFolSource.Items.Restrict(sFilterString)
lCount = objCategoryMails.Count
'Start 1st Method not working, - if new mail comes in between the loop in the source folder (Inbox)
'Then below code left some mails, and not looping to the desired mail
If lCount > 0 Then
For Each ObjItem In objCategoryMails
Set objItem2 = ObjItem.Copy
objItem2.Move objFolDes_1
Set objItem2 = ObjItem.Copy
objItem2.Move objFolDes_2
ObjItem.Delete
Next ObjItem
End If
'End ====
'Start 2nd Method not working, - if new mail comes in between the loop in the source folder (Inbox)
'The Index of the looping mail changed and generating the error message when trying to delete the looped mail, after copy and move.
If lCount > 0 Then
For lLoop = lCount To 1 Step -1
Set ObjItem = objCategoryMails(lLoop)
Set objItem2 = ObjItem.Copy
objItem2.Move objFolDes_1
Set objItem2 = ObjItem.Copy
objItem2.Move objFolDes_2
ObjItem.Delete
Next lLoop
End If
'End ====
'Start 3rd Method not working, - if new mail comes in between the loop in the source folder (Inbox)
'Then below code is not looping some mails, that should have included in the loop
For Each ObjItem In objFolSource.Items
If ObjItem.Categories = vbNullString And ObjItem.Class = olMail Then
Set objItem2 = ObjItem.Copy
objItem2.Move objFolDes_1
Set objItem2 = ObjItem.Copy
objItem2.Move objFolDes_2
ObjItem.Delete
End If
Next ObjItem
'End ====
End Sub
Thanks in advance for reading this mail and for your help.
As always God bless you.
Thanks
Kashif
Hi Kashif! I don't have a lot of experience with Outlook but I wonder if it might be better to do this task using either the NewMail event or the ItemAdd event?
docs.microsoft.com/en-us/office/vba/api/outlook.application.newmail
docs.microsoft.com/en-us/office/vba/api/outlook.items.itemadd
These events are triggered automatically when either a new mail item is received or an item is added to a folder.
Hi Andrew, thanks for the reply, ok so in between the for each loop how can we trigger an event and how can that new email will add automatically in running for each loop?
Thanks
Kashif
@@kashifkhanspecial Hi Kashif, you won't need to trigger the event - it will be triggered automatically when the event happens. The information on the pages I linked to earlier describe how to use a class module to initialise the event handler for these events. The ItemAdd event looks like the best one to use because it has a parameter called Item which provides you with access to the item that has just been added to the folder. So you don't need to loop over all of the items each time - your code will deal with each item individually. You can set the ItemAdd event to be triggered on the Inbox folder.
I haven't done this myself so I can't guide you any more than that.
Hi Andrew, Thank you so much for the reply, I will try my level best to implement your suggestion.
Thanks again, take care and have a nice and healthy day ahead.
Thanks
Kashif
@@kashifkhanspecial Good luck Kashif, I hope you get it to work!
I am from a french spoken country and in french when you are speechless you just say: "Oh!"
Hi Thank you so much for the information. I have a requirement user has to provide the serching key words. When I am trying to give like this i am getting error as " Cannot parse conditions. Error at "@SQL="urn:schemas:httpmail:textdescript.....
I have mentioned as
Filterstring = "@SQL=""urn: schemas: httpmail:textdescription" & Sheet1.Range("A1").value
User input:
In A1 = "Like '%Hi Lyndy%'"
If you can suggest me it would be very helpful
Hi Prasanth, you need to include the closing quotes for the textdescription property and a space to follow it. Something like this:
Filterstring = "@SQL=""urn: schemas: httpmail:textdescription"" " & Sheet1.Range("A1").value
I hope it helps!
@@WiseOwlTutorials Thank you so much For your Quick Response. Yes problem resolved i missed the space ☺️ in SQL Query. Thanks a lot. It helped me a lot
@@prasantha1972 Happy to hear that it helped!