Macro to save file and send emails? Help

[ForrestGump01]

Hi all,
I'm developing an excel template for my team to use to write up purchase orders and send a receipt to our client, and send our purchase orders to our vendors (distribution). I need help writing a macro that:

1) Saves the file in a specified location
2) Uses that file save location (dynamic) as a reference to find attachments
3) Writes and sends multiple emails to different vendors/our client
4) Copies a row of data and pasts it in another workbook

The process would look something like: I click the macro, it opens a window to select the file save location -> File save location is denoted somewhere in the workbook -> The macro then saves multiple PDFs (purchase orders) in that file save location -> The macro allows the user to select additional files within the file save location to attach to emails (multiple emails with different attachments) -> The macro sends each email to respective recipient (each recipient and respective body text are on their own sheet) -> The macro then copies data from another sheet, opens another workbook saved on our network drive, and pastes that data in that file, saves that file, and closes the original workbook.

Can anyone help me with writing the different pieces of this macro? I think I can figure the functionality of sending the emails on my own, and copy-pasting the data in the final step. It's the user interfaces and attachments that I'm most stuck on...

All help is greatly appreciated!

 

[emavelo]

Sub SendEmailsAndCopyData()
    Dim OutApp As Object
    Dim OutMail As Object
    Dim strbody As String
    Dim sig As String
    Dim save_path As String
    Dim attachment_path As String
    Dim wb As Workbook
    Dim wb2 As Workbook
    Dim ws As Worksheet
    Dim ws2 As Worksheet
    Dim rng As Range
    Dim row_to_copy As Integer
    
    ' Prompt user to select file save location
    With Application.FileDialog(msoFileDialogFolderPicker)
        .Title = "Select file save location"
        .Show
        If .SelectedItems.Count = 0 Then Exit Sub
        save_path = .SelectedItems(1)
    End With
    
    ' Save the current workbook in the selected location
    ActiveWorkbook.SaveAs Filename:=save_path & "\" & ActiveWorkbook.Name, _
        FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
    
    ' Find attachments in the selected location
    attachment_path = save_path & "\"
    attachment_filetype = "*.pdf" ' Change this to the file type of your attachments
    attachment_file = Dir(attachment_path & attachment_filetype)
    
    ' Start a loop to send emails to different recipients
    For i = 1 To 3 ' Change this to the number of emails you want to send
        ' Create a new email
        Set OutApp = CreateObject("Outlook.Application")
        Set OutMail = OutApp.CreateItem(0)
        
        ' Define the email body and signature
        strbody = ThisWorkbook.Sheets("Emails").Range("B" & i).Value
        sig = vbCrLf & vbCrLf & "Best regards," & vbCrLf & "Your Name"
        
        ' Attach the files
        Do While attachment_file <> ""
            OutMail.Attachments.Add attachment_path & attachment_file
            attachment_file = Dir
        Loop
        
        ' Add the email body and signature
        With OutMail
            .To = ThisWorkbook.Sheets("Emails").Range("A" & i).Value
            .Subject = ThisWorkbook.Sheets("Emails").Range("C" & i).Value
            .Body = strbody & sig
            .Send
        End With
        
        ' Clear the email object and reset attachment_file for next loop
        Set OutMail = Nothing
        Set OutApp = Nothing
        attachment_file = Dir(attachment_path & attachment_filetype)
    Next i
    
    ' Copy data from another sheet and paste it into another workbook
    Set wb = ThisWorkbook
    Set ws = wb.Worksheets("Data")
    Set wb2 = Workbooks.Open("\\network\drive\Workbook2.xlsx")
    Set ws2 = wb2.Worksheets("Sheet1")
    
    row_to_copy = 2 ' Change this to the row number you want to copy
    Set rng = ws.Rows(row_to_copy)
    
    rng.Copy ws2.Rows(row_to_copy)
    wb2.Save
    wb2.Close