Macro Excel : create an excel for each city and send it to the email adresse

MicrosoftNewbie121 

To accomplish this task, you can use Excel VBA to create separate workbooks for each city and then send each workbook as an email attachment to the corresponding email address. The following VBA code will help you automate this process. Ensure you have the required references for Outlook in your VBA environment.

VBA Code to Create and Send Excel Workbooks

1. Open Excel and Press Alt + F11 to open the VBA editor.
2. Insert a New Module by clicking Insert > Module.
3. Copy and Paste the Following Code into the module.

Here is a sample VBA script to achieve this:

Vba Code is untested backup your file. The inserting file was not open, for my own security reasons.

Sub CreateAndSendWorkbooks()
    Dim ws As Worksheet
    Dim cityRange As Range, cell As Range
    Dim cityName As String, emailAddress As String
    Dim newWorkbook As Workbook
    Dim newWorksheet As Worksheet
    Dim lastRow As Long
    Dim OutlookApp As Object
    Dim OutlookMail As Object

    ' Initialize Outlook
    Set OutlookApp = CreateObject("Outlook.Application")

    ' Set the worksheet containing your data
    Set ws = ThisWorkbook.Sheets("Sheet1") ' Change Sheet1 to the name of your data sheet

    ' Define the range with cities and email addresses
    Set cityRange = ws.Range("A2:A" & ws.Cells(ws.Rows.Count, 1).End(xlUp).Row)

    ' Loop through each city
    For Each cell In cityRange
        cityName = cell.Value
        emailAddress = cell.Offset(0, 1).Value ' Adjust if email is not in the next column

        ' Create a new workbook for each city
        Set newWorkbook = Workbooks.Add
        Set newWorksheet = newWorkbook.Sheets(1)
        newWorksheet.Name = cityName

        ' Copy the header
        ws.Rows(1).Copy Destination:=newWorksheet.Rows(1)

        ' Filter and copy data for each city
        ws.Range("A1").AutoFilter Field:=1, Criteria1:=cityName
        lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row
        ws.Range("A2", "C" & lastRow).SpecialCells(xlCellTypeVisible).Copy Destination:=newWorksheet.Rows(2)

        ' Remove filter
        ws.AutoFilterMode = False

        ' Save the new workbook
        newWorkbook.SaveAs ThisWorkbook.Path & "\" & cityName & ".xlsx"

        ' Create and send email
        Set OutlookMail = OutlookApp.CreateItem(0)
        With OutlookMail
            .To = emailAddress
            .Subject = "Data for " & cityName
            .Body = "Please find attached the data for " & cityName & "."
            .Attachments.Add newWorkbook.FullName
            .Send
        End With

        ' Close and save the new workbook
        newWorkbook.Close SaveChanges:=False
    Next cell

    ' Clean up
    Set OutlookMail = Nothing
    Set OutlookApp = Nothing

    MsgBox "Emails sent successfully!"
End Sub

Explanation of the Code

1. Initialize Outlook: This section initializes the Outlook application.
2. Set the Worksheet: The code sets the worksheet containing your data.
3. Define the Range: This defines the range containing city names and email addresses.
4. Loop Through Each City: The code loops through each city, creates a new workbook, and filters data for the specific city.
5. Copy Header and Data: Copies the header and the filtered data to the new workbook.
6. Save the Workbook: Saves the new workbook with the city name.
7. Send Email: Creates and sends an email with the workbook as an attachment to the corresponding email address.
8. Clean Up: Closes the workbook and cleans up the objects.

Ensure Required References

• Outlook Library Reference: You need to set a reference to the Microsoft Outlook Object Library. In the VBA editor, go to Tools > References and check Microsoft Outlook XX.X Object Library.

Important Notes

• Adjust the Column Offsets: Adjust the column offsets (cell.Offset(0, 1).Value) based on the actual position of your email addresses.
• File Path: Ensure that the file path for saving workbooks is accessible and has write permissions.
• Email Addresses: Ensure that email addresses are correct to avoid sending errors.

By following these steps and using the provided VBA code, you can automate the creation and emailing of city-specific Excel files from a master inventory sheet. The text, steps and the vba code were created with the help of AI.

My answers are voluntary and without guarantee!

Hope this will help you.

Was the answer useful? Mark as best response and Like it!

This will help all forum participants.