Split Specific Sheets into New Excel Workbook and email to different people

[Excel2021]

Hello,

I have an Excel Workbook with 30 sheets and I would like to select specific sheets and turn them into new Workbooks which will then get sent out to specific people via email. Is this possible via VBA?


As an example, say I had a workbook with the following sheet names:

AppleUSD
AppleCAD
AppleGBP
OrangeUSD
OrangeCAD
OrangeGBP

I would like 2 seperate workbooks to be created automatically. 1) called "Apple" which would includes the 3 Apple sheets above and 2) called "Orange" which includes the 3 Orange sheets above.

if possible, next, I would like the newly created "Apple" Workbook to be sent with the email title "Apple" and the message saying "Please see the Attached file" to Bb@gmail.com and Ss@gmail.com. Similarly I would like the "Orange" workbook sent to PL@gmail.com and cc GD@gmail.com with the email title "Orange" and the message saying "Please see the Attached file".


Thanks in advance.

 

[Excel2021]

I don't attach files via SharePoint but have read you could try using the replace statement. I haven't tested this:

.Attachments.Add(ThisWorkbook.Path & "\" & "the name of the new file.xlsx") = Replace(ThisWorkbook.Path & "\" & "the name of the new file.xlsx", "&20", " ")

Its now double attaching the file with that code. One file is named:

NA%20Oranges%20&%20Apples.xlsx

and the other shows as:

SharepointFilePath/NA%20Oranges%20&%20Apples.xlsx

I was able to adjust your code to:

  .Attachments.Add(ThisWorkbook.Path & "\" & "the name of the new file.xlsx") = Replace("the name of the new file.xlsx", "%20", " ")

This brings in 2 files again but this time one is named:

NA%20Oranges%20&%20Apples.xlsx

And the other shows correctly as:

NA Oranges & Apples.xlsx

If I can figure out how to remove the

NA%20Oranges%20&%20Apples.xlsx attachment it will work correctly.

Thanks

 

[Excel2021]

Its now double attaching the file with that code. One file is named:

NA%20Oranges%20&%20Apples.xlsx

and the other shows as:

SharepointFilePath/NA%20Oranges%20&%20Apples.xlsx

I was able to adjust your code to:

  .Attachments.Add(ThisWorkbook.Path & "\" & "the name of the new file.xlsx") = Replace("the name of the new file.xlsx", "%20", " ")

This brings in 2 files again but this time one is named:

NA%20Oranges%20&%20Apples.xlsx

And the other shows correctly as:

NA Oranges & Apples.xlsx

If I can figure out how to remove the

NA%20Oranges%20&%20Apples.xlsx attachment it will work correctly.

Thanks

Actually, when I went to open the file named "NA Oranges & Apples.xlsx" the actual file reverts to NA%20&%20Apples.xlsx. Only the display name in outlook changes the actual file stays the same and is duplicated.

 

[Trevor G]

On your desktop and the folder, right click to get the properties up and look at the folder path. Is it different to the sharepoint path? If so then try swapping to this path. Only other alternative I can suggest is to use a dash inbetween the file name to remove the spaces. Example:

C:\desktop\myfolder\workbook-orange-applex.xlsx

 

[Excel2021]

Actually, when I went to open the file named "NA Oranges & Apples.xlsx" the actual file reverts to NA%20&%20Apples.xlsx. Only the display name in outlook changes the actual file stays the same and is duplicated.

I figured out how to bypass sharepoint and saved the file locally. This fixed it.

Actually, when I went to open the file named "NA Oranges & Apples.xlsx" the actual file reverts to NA%20&%20Apples.xlsx. Only the display name in outlook changes the actual file stays the same and is duplicated.

I worked on this some more and figured out how to bypass Sharepoint by changing the default save setting in excel and moving the file to a folder that is local. This solved the issue. Don't worry about adjusting the code. I am going to be focusing on the signature when I get a chance.

Thanks.

On your desktop and the folder, right click to get the properties up and look at the folder path. Is it different to the sharepoint path? If so then try swapping to this path. Only other alternative I can suggest is to use a dash inbetween the file name to remove the spaces. Example:

C:\desktop\myfolder\workbook-orange-applex.xlsx

I was able to save it locally which fixed the issue. The issue I am having now is that I have one workbook where I am trying to combine 20+ sheets into one using the code you gave me but Excel will often crash. Is there a way to alter your code so that I can copy the whole workbook and remove 2 sheets instead of trying to combine 20+ individual sheets? I am still using the original code to combine other workbooks in the file which are under 10 sheets.

Thanks