MACRO EXPERTS - NEED TO KNOW IF YOU CAN ASSIST

[erica3taylor]

I have folders set up by manufacturer name and in those folders are there monthly invoices that are billed to them. I want a macro to be able email all invoices in each individual MFG folder to the respective MFG. Is there a way to set up a macro that can do this and possibly prompt me to input the email address to send it to or do this automatically instead of having to change each code to reflect a new email address.

So far I use this code, but I would have to change the email address each time i send to someone different and I want to avoid that and make this more automated.

Sub SendAllFilesInSeparateEmails()

Dim objShell As Object

Dim objWindowsFolder As Object

Dim objFile As Object

Dim strWindowsFolder As String

Dim objFileSystem As Object

Dim objMail As Outlook.MailItem



'Select a Windows folder

Set objShell = CreateObject("Shell.Application")

Set objWindowsFolder = objShell.BrowseForFolder(0, "Select a Windows Folder:", 0, "")



If Not objWindowsFolder Is Nothing Then

strWindowsFolder = objWindowsFolder.self.Path & "\"

Set objFileSystem = CreateObject("Scripting.FileSystemObject")

Set objWindowsFolder = objFileSystem.GetFolder(strWindowsFolder)



'Send each file in an email

For Each objFile In objWindowsFolder.Files



'Create a new mail

Set objMail = Outlook.Application.CreateItem(olMailItem)

'Change the details as per your needs

With objMail

.Subject = Left(objFile.Name, Len(objFile.Name) - (Len(objFileSystem.GetExtensionName(objFile.Name)) + 1))

.Attachments.Add objFile.Path

.Recipients.Add ("etaylor@ultradst.com")

.Recipients.ResolveAll

.Send

End With

Next



'Prompt you when completing sending

MsgBox "All done!", vbOKOnly + vbExclamation

End If

End Sub

any experts? Your assistance is greatly appreciated.

 

[JEC]

Yes that is all possible, if you just replace all with this code.
The only thing you need to do is creating a dynamic email address. You could extract that from the file

Sub jec()
 Dim it As Variant
 Application.ScreenUpdating = False
 
 With Application.FileDialog(msoFileDialogFolderPicker)
    If .Show Then
      For Each it In CreateObject("scripting.filesystemobject").getfolder(.SelectedItems(1)).Files
          With CreateObject("outlook.application").createitem(0)
            .to = "your dynamic email address for the specific file"
            .cc = "test"
            .Subject = "test"
            .body = "test"
            .attachments.Add it.Path
            .display  '.send
         End With
      Next
    End If
 End With
End Sub

 

[erica3taylor]

Yes that is all possible, if you just replace all with this code.
The only thing you need to do is creating a dynamic email address. You could extract that from the file

Sub jec()
 Dim it As Variant
 Application.ScreenUpdating = False
 
 With Application.FileDialog(msoFileDialogFolderPicker)
    If .Show Then
      For Each it In CreateObject("scripting.filesystemobject").getfolder(.SelectedItems(1)).Files
          With CreateObject("outlook.application").createitem(0)
            .to = "your dynamic email address for the specific file"
            .cc = "test"
            .Subject = "test"
            .body = "test"
            .attachments.Add it.Path
            .display  '.send
         End With
      Next
    End If
 End With
End Sub

Thank you. How do I create a dynamic email address? What is that for?

 

[erica3taylor]

Thank you. How do I create a dynamic email address? What is that for?

Do I need to create an excel spreadsheet for all contact emails?

 

[JEC]

Your goal is to send emails to the one who is addressed to the specific file right?

 

[erica3taylor]

Your goal is to send emails to the one who is addressed to the specific file right?

My goals is to send emails to the specific mfg that the folder is for but to be able to do it all others as well

 

[JEC]

Yes so every file goes to an other email address, right?
If these files are Excel files, you could put their email addresses in that file, after which you can extract them (within the loop)

 

[erica3taylor]

Yes so every file goes to an other email address, right?
If these files are Excel files, you could put their email addresses in that file, after which you can extract them (within the loop)

yes but all files are pdf. There is a main folder then a subfolder for each MFG where each other invoices are stored

 

[JEC]

There are more ways for doing this. You can write code in which you specify your emailaddresses. Then link them to their own “MFG” email address.

Somehow you need to link the files to an email address, I can’t look into your explorer unfortunately

 

[erica3taylor]

Thank you!!