[Solved] split excel file into multiple excel file and sending out the files by outlook

[chteoh]

Good day to everyone.
I am not very familiar with VPA and learn the coding by record Macro and online comment.
I have one request for solution using VBA to split the file into multiple excel files based on specific column( i hope that can be variable based on user setting) and sending out the files by outlook/ gmail selection.
Here is the example of data and after the split, macro is sending out by outlook to below email address.

Inventory ID	Vendor ID	Email address	PO No	Quantity
D0001-01-01	L0056T	[email protected]	80262	2000​
R0001-01-01	T0074T	[email protected]	78946	4000​
V3000-01-01	V0045T	[email protected]	56798	3000​

The split filename can be original file name_split column data_current date
i hope that macro can extract the standard note from one sheet before sending out the email.
Attached is the details layout
Thanks a lot



Email subject is follow after split file name and there is a note indicated the email.

 

[Aniruddah Alam]

Good day to everyone.
I am not very familiar with VPA and learn the coding by record Macro and online comment.
I have one request for solution using VBA to split the file into multiple excel files based on specific column( i hope that can be variable based on user setting) and sending out the files by outlook/ gmail selection.
Here is the example of data and after the split, macro is sending out by outlook to below email address.

Inventory ID	Vendor ID	Email address	PO No	Quantity
D0001-01-01	L0056T	[email protected]	80262	2000​
R0001-01-01	T0074T	[email protected]	78946	4000​
V3000-01-01	V0045T	[email protected]	56798	3000​

The split filename can be original file name_split column data_current date
i hope that macro can extract the standard note from one sheet before sending out the email.
Attached is the details layout
Thanks a lot



Email subject is follow after split file name and there is a note indicated the email.

Hello Chteoh,

I have created a file with VBA code that can help you split your main file into a smaller one that contains only the row of the selected cell.

To use the file, simply go to the Sample Data sheet and click on the button provided. This will open up an input box that will prompt you to select a cell that contains the Vendor ID. Once you have selected the cell, the VBA code will create a new workbook with the entire row of the selected cell. The new workbook will be named as follows: MainFile_VendorID_Date.xlsx.

Once the new file has been created, it will be sent to the corresponding email address with a subject name that you can customize from the VBA code. It's important to keep in mind that the email will be sent from the Outlook app on your computer, so you will need to have Outlook installed beforehand.

I hope that this information is helpful. If you have any other questions or concerns, please don't hesitate to reach out to us.

Regards
Aniruddah
Team Exceldemy

 

[chteoh]

Hi Mr Aniruddah,
Thanks so much for your reply.
I am sorry being late reply. I hope to elaborate more on my request.

Below is my example of raw data for PO and the actual row can be >10K PO with >2K of different vendor ID.

Inventory ID	Vendor ID	Email address	PO No	Quantity
D0001-01-01	L0056T	[email protected]	80262	2000
R0001-01-01	T0074T	[email protected]	78946	4000
V3000-01-01	V0045T	[email protected]	56798	3000
Z3456-01-02	T0074T	[email protected]	34678	3000
H3456-01-02	V0045T	[email protected]	26890	3000
A3456-01-02	T0074T	[email protected]	26258	5000
DZ3456-01-02	L0056T	[email protected]	36908	4000

I hope to have a macro to split the file into multiple different files by specific split column (this is a variable column that can be defined by user). For the above case study, we indicate column B (vendor ID) so macro to split into 3 files which naming convention is filename_column split data_today date.
In this case I should have 3 files in the same folders that I have master file as per below example.


after this I hope to send out the respective file to using outlook to respective email address with a standard note retrieve from master file that user to key in as below and email subject is as per filename. The email will be to outbox so it will allow user to confirm before it is sent.
example email subject is



Thanks a lot for your help and I shall attach the master file that user need to enter

				​
				​
				​
				​
				​
				​
				​

 

[chteoh]

Hi all,
I managed to find the coding in the website for split the excel file into multiple files but fix to column A only.
Now I am lacking of auto save with correct filename and populate the email standard note with correct email subject based on filename.
Will keep the team updated if there is any new finding.

Sub splitfile()
Dim objWorksheet As Excel.Worksheet
Dim nLastRow, nRow, nNextRow As Integer
Dim strColumnValue As String
Dim objDictionary As Object
Dim varColumnValues As Variant
Dim varColumnValue As Variant
Dim objExcelWorkbook As Excel.Workbook
Dim objSheet As Excel.Worksheet
Set objWorksheet = ActiveSheet
nLastRow = objWorksheet.Range("A" & objWorksheet.Rows.Count).End(xlUp).Row
Set objDictionary = CreateObject("Scripting.Dictionary")
For nRow = 2 To nLastRow
strColumnValue = objWorksheet.Range("A" & nRow).Value
If objDictionary.Exists(strColumnValue) = False Then
objDictionary.Add strColumnValue, 1
End If
Next
varColumnValues = objDictionary.Keys
For i = LBound(varColumnValues) To UBound(varColumnValues)
varColumnValue = varColumnValues(i)
Set objExcelWorkbook = Excel.Application.Workbooks.Add
Set objSheet = objExcelWorkbook.Sheets(1)
objSheet.Name = objWorksheet.Name
objWorksheet.Rows(1).EntireRow.Copy
objSheet.Activate
objSheet.Range("A1").Select
objSheet.Paste
For nRow = 2 To nLastRow
If CStr(objWorksheet.Range("A" & nRow).Value) = CStr(varColumnValue) Then
objWorksheet.Rows(nRow).EntireRow.Copy
nNextRow = objSheet.Range("A" & objWorksheet.Rows.Count).End(xlUp).Row + 1
objSheet.Range("A" & nNextRow).Select
objSheet.Paste
objSheet.Columns("A:F").AutoFit
End If
Next
Next
End Sub

 

[Aniruddah Alam]

Hi all,
I managed to find the coding in the website for split the excel file into multiple files but fix to column A only.
Now I am lacking of auto save with correct filename and populate the email standard note with correct email subject based on filename.
Will keep the team updated if there is any new finding.

Sub splitfile()
Dim objWorksheet As Excel.Worksheet
Dim nLastRow, nRow, nNextRow As Integer
Dim strColumnValue As String
Dim objDictionary As Object
Dim varColumnValues As Variant
Dim varColumnValue As Variant
Dim objExcelWorkbook As Excel.Workbook
Dim objSheet As Excel.Worksheet
Set objWorksheet = ActiveSheet
nLastRow = objWorksheet.Range("A" & objWorksheet.Rows.Count).End(xlUp).Row
Set objDictionary = CreateObject("Scripting.Dictionary")
For nRow = 2 To nLastRow
strColumnValue = objWorksheet.Range("A" & nRow).Value
If objDictionary.Exists(strColumnValue) = False Then
objDictionary.Add strColumnValue, 1
End If
Next
varColumnValues = objDictionary.Keys
For i = LBound(varColumnValues) To UBound(varColumnValues)
varColumnValue = varColumnValues(i)
Set objExcelWorkbook = Excel.Application.Workbooks.Add
Set objSheet = objExcelWorkbook.Sheets(1)
objSheet.Name = objWorksheet.Name
objWorksheet.Rows(1).EntireRow.Copy
objSheet.Activate
objSheet.Range("A1").Select
objSheet.Paste
For nRow = 2 To nLastRow
If CStr(objWorksheet.Range("A" & nRow).Value) = CStr(varColumnValue) Then
objWorksheet.Rows(nRow).EntireRow.Copy
nNextRow = objSheet.Range("A" & objWorksheet.Rows.Count).End(xlUp).Row + 1
objSheet.Range("A" & nNextRow).Select
objSheet.Paste
objSheet.Columns("A:F").AutoFit
End If
Next
Next
End Sub

Hi Chteoh,

I've created a VBA code that will help you achieve what you need. I've attached the file here. To run the code, use the button provided on the worksheet.

As per your request, this time the emails won't be sent directly, but instead, they will be saved in the Draft section. This way, you can review and modify them before sending them manually from the draft section of Outlook.

Please download and run the code on your computer. Once you've had a chance to test it, let me know if you need further assistance.

Best regards,
Aniruddah
Team Exceldemy

 

[chteoh]

Yes it did work as per my requirement.
Thank you so much Mr Aniruddah.

 

[Aniruddah Alam]

Yes it did work as per my requirement.
Thank you so much Mr Aniruddah.

You're very welcome! I'm thrilled that it worked for you. Don't hesitate to come back to Exceldemy for any of your Excel-related needs. We're always here to help!
Best regards,
Aniruddah
Team Exceldemy