Send Email from Specific File Path and Reference Text in Cell

[skierpro720]

I am trying to send files to email addresses in cell A1 of a file within a folder path. Cell A1 of every file contains an email address. I am struggling to call the text in that cell in the ".To" section of my script. In addition, the second "With" statement ends up attaching files 1 and 2, rather that file 2 exclusively. I would also like to disable the Outlook security warning I receive about another program trying to access Outlook. I would appreciate any help!


Sub Send_File_Outlook()


Dim OutApp As Object
Dim OutMail As Object
Dim strbody As String
Dim SubjectLine As String

SubjectLine = "Subject A"

Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)
Application.DisplayAlerts = False

strbody = "Line 1" & vbNewLine & vbNewLine & _
"Line 2"



On Error Resume Next
With OutMail
.To = Range("A1").("\\Filepath\FileName_1.xlsx")
.CC = ""
.BCC = ""
.Subject = SubjectLine
.Body = strbody
.Attachments.Add ("\\Filepath\FileName_1.xlsx")
.Display
.Send
End With
With OutMail
.To = Range("A1").("\\Filepath\FileName_2.xlsx")
.CC = ""
.BCC = ""
.Subject = SubjectLine
.Body = strbody
.Attachments.Add ("\\Filepath\FileName_2.xlsx")
.Display
.Send
End With
End Sub

 

[Fluff]

Duplicate https://www.mrexcel.com/forum/excel...l-specific-file-path-reference-text-cell.html

Please do not post the same question multiple times. All clarifications, follow-ups, and bumps should be posted back to the original thread.
Per forum rules, posts of a duplicate nature will be locked or deleted (rule 12 here: Forum Rules).

Note that sometimes posts from new users require Moderator approval before you can see them on the public forums. When this happens, you should see a message to that effect when you try to post it.
Please be patient and do not attempt to post the question again.