How to run code in background

[ThrottleWorks]

Hi,

I am running a macro from Excel.
This macro involves working with Excel, Word, PDF and Outlook.
In short, my macro is exporting e-mails to PDF.

Please see below link for your reference.
https://chandoo.org/forum/threads/how-to-save-e-mail-as-pdf-with-excel-vba.40703/

Macro takes time to complete the entire operation, minimum 1 minute if there is even single e-mail to export.

Please note, if for one e-mail if macro takes 1 minute then it does not mean for 6 e-mails it will take 6 minutes.

Macro might take less than 6 minutes or more than 6 minutes but usually more than 6 minutes to export 6 different e-mails.

But it is certainly slow.

My query is, will it be possible to run this macro in background so user can work on at least Outlook and Excel simultaneously.

So, at the same time, macro will be running and user will be working manually on e-mails or preparing report manually in excel.

What do I need to change in my code. Will it be possible.
Can anyone please help me this.

 

[vletm]

ThrottleWorks
Have You test to add eg DoEvents in 'slow loops or so'?

ps Still that 'print to PDF' would be quicker ... if need to hurry.​

 

[Chihiro]

Other than opening separate instances of application. I don't think this is possible.

And since... Outlook only allows single instance... your best bet is to just open Web client for Outlook and work from there while code is running.

Edit: Older version of Outlook may be able to open multiple instance. But latest version (2016/365?) allows single instance only. Excel, you can open multiple instance by holding ALT key when opening Excel, until pop up shows up. It will ask you if you want to open a new instance.

 

[ThrottleWorks]

Hi @vletm sir, thanks a lot for the help.
No, I will Google 'DoEvents' and will post here if I required further help.

Have a nice day ahead.

 

[ThrottleWorks]

Hi @Chihiro sir, thanks a lot for the help.
Have a nice day ahead.

PS – Majority of the time of my code is consumed with below two lines.
‘objMail.SaveAs strWordDocument, olDoc’
‘objWordDocument.ExportAsFixedFormat OutputFileName:=MyPDFFile, ExportFormat:=wdExportFormatPDF’

Code line no 221 and 258 of the attached file in my original post.
I am saving e-mail as word file with ‘objMail.SaveAs strWordDocument, olDoc’ line and this is taking time.

Could you please review attached file if you get time and advise for better solution (only if time permits you).

If could save time here, that will also help me a lot.

 

[Chihiro]

Not really. Only other process that I'm aware of is only available to Windows 10 (Print as PDF).

If the format isn't vital, you could just save as Outlook mail format (Unicode).

Without knowing your entire use case. Hard to comment on the process.

 

[ThrottleWorks]

Hi @Chihiro sir, thanks a lot for the help.
Sorry for late reply.

The idea is to pick latest for the day for a particular subject from the selected outlook folder.
And export it as PDF.

We have an input box in the macro.
User will type values for subject line in input box.
We have folder picker option provided in the macro.
User will select outlook folder from which he needs to search e-mail with that particular subject.

For all the e-mails containing subject line from the selected folder, I need to check latest e-mail for each day and export it as PDF.

Please see below example for your reference.

User has typed ‘test’ as subject line in the folder.

Folder selected by folder picker is Chandoo.


I have 2 e-mails each for 28th and 27th with subject containing as test in folder Chandoo.

Now code will export two e-mails as PDF, one latest e-mail from 27 and one from 28.


Hope this helps.




Have a nice day ahead.