Filter Get Outlook Mail Message according to dates selected by user

Hi All,

I’m trying to add an email filter such that it will only retrieve emails from the start and end date indicated by the user. I have tried this code but its not working

“@SQL=urn:schemas:httpmail:datereceived >= ‘StartDate’ AND urn:schemas:httpmail:datereceived <= ‘EndDate’ AND urn:schemas:httpmail:subject LIKE ‘%AIM Alerts Update%’”

Can I also check if there is a way users can select a certain date using a drop-down calendar in the input dialog

Cheers

Hi,

Are StartDate and EndDate string type variable of datatime? if so the following expression may help you.

$"@SQL=urn:schemas:httpmail:datereceived >= '{StartDate}' AND urn:schemas:httpmail:datereceived <= '{EndDate}' AND urn:schemas:httpmail:subject LIKE '%AIM Alerts Update%'"

Regards,

Hi @lqxchan

' Input Dialog for Start Date
InputDialog1 (Output: StartDateStr)

' Input Dialog for End Date
InputDialog2 (Output: EndDateStr)

StartDate As DateTime variable
EndDate As DateTime variable


StartDate = DateTime.Parse(StartDateStr).ToString("yyyy-MM-dd")
EndDate = DateTime.Parse(EndDateStr).ToString("yyyy-MM-dd")

In the OutLook Filter
"@SQL=urn:schemas:httpmail:datereceived >= '" & StartDate & "' AND urn:schemas:httpmail:datereceived <= '" & EndDate & "' AND urn:schemas:httpmail:subject LIKE '%AIM Alerts Update%'"

Hi,

I tried this but the date received function didn’t work as the bot still retrieved emails outside the range. Is there a specific date format that the user has to input?

Cheers,

Hi,

I tried this method but there is an error with the assign activity

@lqxchan

System.DateTime.Parse(Variable).ToString("yyyyy-MM-dd")

Hi,

It may be necessary to use UTC. Can you try to create your datetime string as the following?

startDate = DateTime.Now.ToUniversalTime.ToString("yyyy/M/d HH:mm")

Regards,

Hi,

My input dialog looks like this. Kindly advise if there are any changes to be made
image

Hi,

How about to use the following expression to convert yyyy/M/d datetime format?

StartDate = DateTime.ParseExact(StartDate,"d/M/yyyy",System.Globalization.CultureInfo.InvariantCulture,System.Globalization.DateTimeStyles.None).ToString("yyyy/M/d")

Regards,

Hi,

It works. Thanks!

Can I also check if there is a way to add a restriction for the inputs in the input dialog such that the code doesn’t run if the format keyed is wrong

Hi,

If you want to check whether input is wrong for date time and have user re-input when it’s wrong, the following may help you.

not DateTime.TryParseExact(StartDate,"d/M/yyyy",System.Globalization.CultureInfo.InvariantCulture,System.Globalization.DateTimeStyles.None,New DateTime)

Regards,

Hi,

I have another issue regarding adding column headers/names such as “Invoice 1”, “Invoice 2”… for each non-empty columns after transposing my data. How should I go about doing this?

Cheers

Hi,

it seems you already raised the topic as the following. Perhaps you should share more specific information (input and expected output etc as screenshot or file) in the topic, if possible. It’s no problem if dummy data.

Regards,

Hi,

Our initial data has an invoice column which stores multiple invoices separated by a comma. We split the invoices using the text to column activity which will separate the invoices into different columns depending on the number invoices. However, these columns do not have headers/name. hence, we would like to name them as such “Invoice 1”, “Invoice 2”… The image below shows the expected output of the transposed data for 3 invoices.

Thanks for the help

If you want to set name of each column as content of the first row, the following will work.

currentItem.ColumnName = dt.Rows(0)(currentItem.ColumnName).ToString

Hi, I think I meant it like if I am given the Invoices column and then I separated each row below Invoices by its commas, I will get each invoice on its own eg. {v, w, x, y, z} gives me 4 new columns on the right of it. BUT for these columns, they will not be assigned a header above, so how will I be able to automate and name these columns accordingly as “Invoices 1”, … “Invoices 5”
image

then after the splitting by commas, new columns will appear but without the column headers eg. Invoices 1, …, Invoices 5
image

Thanks for the reply!

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.