Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

22/10 - Email multiple sheets from excel.

0

Hi Team,

I have 2 Excel workbooks One Raid Register and the other is an Action Register

The code to email the sheets is identical

The code to email multi sheets is slightly different.

The RAID log emailing macro's work - no issue - for single and multi sheet emailing

The Action Register the single sheet email works and works quite well

The Action Register multi sheet email macro is in essence the same as the RAID multi page email macro but does not work. It gets to the line wbNew.close false then crashes. It will crash all open workbooks.

Spent a few hours looking at it and having no luck. Any help will be greatly appreciated

excel
MS Office Version: O365
posted by: semaj (rep: 4) on Sun Oct 22, 2023 at 7:15 am
Answer
Discuss

Discussion

Hi Semaj

Did you see my revised Answer? Hopefully it shows you how to fix your problem (and hopefully will allow you to mark the Answer as Selected).
John_Ru (rep: 6142) Oct 25, '23 at 10:03 am
Add to Discussion

Answers

0
Selected Answer

Semaj

I think there's something wrong with the links in the worksheet "Lov" (which link back to the Enterprise Action Register - Test.xlsm file).

Sub EmailRaidRegister works fine if you change the line copying muliptle pages to this:

'Create Array of sheet to email
    Worksheets(Array("Document Control", "Dashboard", "Action Register")).Copy '### omitting: , "LoV")).Copy

Also, it you stop the original macro when the file has been created with LoV (before the . Close where macro crashes) then open the dated .xlsx file and click in a Count cell in LoV, Excel crashes.

Revision 24 October 2023:

You can solve the  problem by breaking the links in LoV - this has the added advantage (in my opinion) of "freezing" the report at the time of issue. Just add the lines in bold below (within your rmacro):

    'Create Array of sheet to email
    Worksheets(Array("Document Control", "Dashboard", "Action Register", "LoV")).Copy
    ' Break links in LoV
    With ActiveWorkbook.Worksheets("LoV").UsedRange
        .Copy
        .PasteSpecial Paste:=xlPasteValuesAndNumberFormats
    End With
   

The email should be formed correctly and the (new) workbook should now close without issue.

Hope this fixes your problem- if so, please be sure to mark this  Answer as Selected.

posted by: John_Ru (rep: 6142) on Mon Oct 23, 2023 at 5:42 pm
Discuss

Discussion

Thanks John, I will look at this over the weekend and see how I go and respond next week. Appreciate you spending the time to look at this
semaj (rep: 4) Oct 26, '23 at 7:15 am
Okay Semaj. It worked for me so hope you succeed too.
John_Ru (rep: 6142) Oct 26, '23 at 2:31 pm
Works perfectly thanks John
semaj (rep: 4) Oct 28, '23 at 11:02 pm
Glad it worked for you, Semaj. Thanks for selecting my Answer. 
John_Ru (rep: 6142) Oct 29, '23 at 2:05 am
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login