Replies

I didn’t try to scale them or anything, just did a simple import to see if I could reduce the size of the file. Maybe it’s a windows vs mac issue that the ‘edit – delete’ process works for me but not you.

1 user thanked author for this post.

Julian D A Wiseman

Julian,

I imported your ten markers into an excel file ‘TenMarkers Test orig.xlsx’ which saved at 108k size. I copied the file to ‘TenMarkers Test no png.xlsx’, used 7zip to ‘open archive’, navigated to xl\media folder, deleted all png files then closed/saved the file – this new file saved as size 16.8k.

When I opened ‘TenMarkers Test no png.xlsx’ excel didn’t report any errors and seemed to be fine.

You can get 7zip here: https://www.7-zip.org/download.html.

[EDIT] In you later post you said you’d compressed the png files. Don’t compress the png files, delete them and see how that goes before trying 7zip.

I know it’s still a work around to your issue but best I can do at the moment.

1 user thanked author for this post.

Julian D A Wiseman

Julian, are you able to zip and share an example file for us to experiment with?

Julian, a couple of things you could try towards you’re second dot point/option…

Firstly, try AddPicture2 rather than AddPicture. It has a ‘compress’ option which you can set to ‘msoPictureCompressTrue’.

Secondly, and not ideal from many aspects, I’m assuming you’re using a modern excel file version (e.g. xlsx) in which case after the file is created you can open the file in your favourite zip editor (e.g. 7zip), navigate to xl\media then delete all the png files. In my testing this reduced the file size and didn’t kick up any errors when I re-opened the file.

If I come up with any other ideas I’ll post back here and I’d be interested in your feedback on how either of these options worked for you.

1 user thanked author for this post.

Julian D A Wiseman

That makes sense. At least you’ve got one method of making it work.

1 user thanked author for this post.

shazzad.arla

What error(s) are you getting and at what line are they occuring?

The only other thing I suggest is to step through the code line by line and check that everything is doing what you’d expect.

For example, you could add

Code:

msgbox folder.Items().Count

immediately after the “Set Folder =
Outlook.Session.Folders(MailBoxName).Folders(“Inbox”).Folders(“BD_Invoice”)” line and check that is giving the number of messages in the BD_Invoice folder.

Shazzad,

See attached.

I’ve added a tab with the code in it for referencing purposes but you can delete that once you see what I’ve done. I created a BD_Invoice folder under my inbox and dumped a bunch of messages into it and the attached code worked for me.

Changes are:

• I deleted the loop as we know the direct path.
• I added a test at (new) line 34 to make sure the item is an email (.class = olMail) to ensure that we are not looking for a field that doesn’t exist.

See how the attached works for you.

btbs wrote:

Pst_Folder_Name = “Inbox.BD_Invoice”

Ignore the previous suggestion.

As you know the folder you’re looking for use this:

Code:

Folder = Outlook.Session.Folders(MailBoxName).Folders("inbox").Folders("BD_Invoice")

and delete/comment out everything between and including the line “‘To access a main folder or a subfolder (level-1)” to the “End if” before line “‘To access a main folder or a subfolder (level-1)”.

Rationale: you don’t need to loop through folders to find the one you want when you know the path to it.

Hopefully that helps.

Try

Code:

Pst_Folder_Name = "Inbox.BD_Invoice"

@shazzad.arla,

You haven’t said whether you are placing this code in Excel or Outlook. From the looks of it you are putting it in Excel.

I think you are missing defining outlook, which you can do as follows:

Code:

Dim Outlook As Outlook.Application
Set Outlook = New Outlook.Application

See how that goes

I assume because it is local Outlook 365, all your emails are in a .pst file.

Can you provide a bit more info on what sort of export you want to do. For example, create a hierarchical folder structure that replicates your email folders with each folder containing each email as a .msg file? Or something else?

Does the export process need to be powershell or are you happy with an Outlook VBA solution?

[Edit] See here for examples of Powershell/Outlook integration: https://learn.microsoft.com/en-us/archive/msdn-magazine/2013/march/powershell-managing-an-outlook-mailbox-with-powershell

Try the following macro. Make sure InputTableStart and OutputTableStart are set to the first cell of the heading of each table.

Code:

Sub ProduceOutputTable()
Dim InputTableStart, OutputTableStart As String
Dim NoCustomers, NoProducts, LoopCustomers, LoopProducts, Volume, OutputRow, NoOutputRows, NoOutputCols As Long

'Initialise - update to reflect your data
InputTableStart = "A2"
OutputTableStart = "H2"
OutputRow = 0

'Clear output table but only if there is already data there
If Range(OutputTableStart).Offset(1, 0) <> "" Then
Range(Cells(Range(OutputTableStart).Row + 1, Range(OutputTableStart).Column), _
Cells(Range(OutputTableStart).End(xlDown).Row, Range(OutputTableStart).Column + 2)).Clear
End If

'Determine number of customers and products
NoCustomers = Range(InputTableStart).End(xlToRight).Column - Range(InputTableStart).Column
NoProducts = Range(InputTableStart).End(xlDown).Row - Range(InputTableStart).Row
For LoopCustomers = 1 To NoCustomers
For LoopProducts = 1 To NoProducts
Volume = Range(InputTableStart).Offset(LoopProducts, LoopCustomers)
If Volume > 0 Then 'write output
OutputRow = OutputRow + 1
Range(OutputTableStart).Offset(OutputRow, 0).Value2 = Range(InputTableStart).Offset(0, LoopCustomers).Value2
Range(OutputTableStart).Offset(OutputRow, 1).Value2 = Range(InputTableStart).Offset(LoopProducts, 0).Value2
Range(OutputTableStart).Offset(OutputRow, 2).Value2 = Range(InputTableStart).Offset(LoopProducts, LoopCustomers).Value2
End If
Next 'LoopProducts
Next 'LoopCustomers
Range(OutputTableStart).Select
MsgBox "Done."
End Sub

A very interesting app. As you know there are a large number of encryption algorithms available – are you able to advise which is best?

There are a number of ways to do this.

The simplest way is to put the following formula into StudentList!B1
=IFERROR(MATCH(A1,PleaseRemoveMe!$A$1:$A$3,0),"")
This will give a blank if the student is not in the remove list or the row number that this student’s id is on if they are.

Fill/drag that down over B1:B24. You can then filter for non-blanks in column B and delete those entries. Remove the filter and you’ll have your cleaned up list.

If it is something you will be doing repetitively, you can use a macro to automatically do it (using a different technique though). If the macro is the way you want to go, let us know.

Glad it worked.

I’d love to know what causes it as I’ve had it happen, probably only 2 or 3 times in a decade (not often I know). All seems good, using computer, shutdown but on restart the next morning the ‘temporary account’ issue occurs. It’s probably one of those things I’ll never get to the bottom of but at least I know how to fix it.