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

chteoh

New member
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 IDVendor IDEmail addressPO NoQuantity
D0001-01-01L0056T[email protected]80262
2000​
R0001-01-01T0074T[email protected]78946
4000​
V3000-01-01V0045T[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.
 
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 IDVendor IDEmail addressPO NoQuantity
D0001-01-01L0056T[email protected]80262
2000​
R0001-01-01T0074T[email protected]78946
4000​
V3000-01-01V0045T[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
 

Attachments

  • Main_File.xlsm
    26.9 KB · Views: 6
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 IDVendor IDEmail addressPO NoQuantity
D0001-01-01L0056T[email protected]802622000
R0001-01-01T0074T[email protected]789464000
V3000-01-01V0045T[email protected]567983000
Z3456-01-02T0074T[email protected]346783000
H3456-01-02V0045T[email protected]268903000
A3456-01-02T0074T[email protected]262585000
DZ3456-01-02L0056T[email protected]369084000

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.

1699946885585.png
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

1699947294420.png

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









 

Attachments

  • 1699946685086.png
    1699946685086.png
    21.4 KB · Views: 2
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 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
 

Attachments

  • ExcelInquery_Updated.xlsm
    28.8 KB · Views: 3

Online statistics

Members online
0
Guests online
1
Total visitors
1

Forum statistics

Threads
282
Messages
1,208
Members
485
Latest member
Elk
Top