VBA Outlook mail attachment

[PhysicsGeek2022]

Hi All:

I am creating a spreadsheet that will send automatic email reminders to clients using a VBA Macros code. The code uses outlook. I have created the code and spreadsheet to do everything I'd like except attach individual files and keep my signature in my outlook e-mail. I know it can be done (attach files) as I have attached files this way in a previous version of the code. However, now with this new advanced code I can't get the files to attach. If I add the Attachment.Add function, no e-mail are sent at all. If I take the Attachment.Add function out then my e-mails send fine but without the attachment. I'm hoping someone can help me with this. Below is my code. Thank you!



Public Sub Send_Email_Automatically2()
Dim rngD, rngS, rngT, rngU As Range
Dim ob1, ob2 As Object
Dim LRow, x As Long
Dim l, strbody, rSendValue, mSub As String
On Error Resume Next
Set rngD = Range("P2", Range("p2").End(xlDown))
If rngD Is Nothing Then Exit Sub
Set rngS = Range("I2", Range("i2").End(xlDown))
If rngS Is Nothing Then Exit Sub
Set rngT = Range("S2", Range("s2").End(xlDown))
If rngT Is Nothing Then Exit Sub
Set rngU = Range("U2", Range("u2").End(xlDown))
If rngU Is Nothing Then Exit Sub
strFolder = "X:\Nuclear Medicine\NEW LEAD APRON EXCEL REPORTS\New Lead Apron Logs 11.2022"
LRow = rngD.Rows.Count
Set rngD = rngD(1)
Set rngS = rngS(1)
Set rngT = rngT(1)
Set rngU = rngU(1)
Set ob1 = CreateObject("Outlook.Application")
For x = 1 To LRow
rngDValue = ""
rngDValue = rngD.Offset(x - 1).Value
If rngDValue <> "" Then
If CDate(rngDValue) - Date < 0 Then
rngSValue = rngS.Offset(x - 1).Value
mSub = rngT.Offset(0, -1).Value
l = ""
strbody = ""
strbody = strbody & rngT.Offset(x - 1).Value & l
strbody = strbody
Set ob2 = ob1.CreateItem(0)
With ob2
.Subject = mSub
.To = rngS
.Body = strbody
'.Attachments.Add strFolder & "\" & rngU
.display
End With
Set ob2 = Nothing
End If
End If
Next
Set ob1 = Nothing
End Sub

 

[PhysicsGeek2022]

I tried that as well as that is what I thought it should be but I get the same error message. I tried it a bunch of different ways.

Maybe it is because I am working on my network from home. I'll try it when I go in to the office tomorrow and see if I get a different outcome unless you have any other suggestions.

 

[Anthony47]

Is the file to be attached available from home?

 

[PhysicsGeek2022]

Is the file to be attached available from home?

Yes

 

[Anthony47]

You mean that drive X:\ is available both from home and from the office?

 

[PhysicsGeek2022]

You mean that drive X:\ is available both from home and from the office?

I can get into the files on the X: Drive but it doesn't appear as an option from my PC when I go into the documents on my computer. When I do click on the link to the document in my spreadsheet I get the following message before I can access the file. So maybe there is some sort of firewall or something? I work in healthcare so it may work tomorrow in the office.

 

[Anthony47]

Try from the office, and be sure that those files are mapped on the "X:" drive

 

[PhysicsGeek2022]

Try from the office, and be sure that those files are mapped on the "X:" drive

Thanks. I'll let you know tomorrow afternoon. I know for sure they are mapped on the X: drive.

BTW. Do you know why my outlook signature disappears? It's not that big of a deal but I thought it might be an easy fix.

 

[Anthony47]

For the signature you have to use the html format. I thought you was using l = "SomeTextHere" for the signature:

l = vbCrLf & "Signature line #1" & vbCrLf & "Signature line #2"
strbody = ""                                                        'This is useless
strbody = strbody & rngT.Offset(x - 1).Value & l                    'This append the signature
strbody = strbody                                                   'This is useless

Please use tags to make your code more readable: select the code, press the VBA icon

 

[PhysicsGeek2022]

For the signature you have to use the html format. I thought you was using l = "SomeTextHere" for the signature:

l = vbCrLf & "Signature line #1" & vbCrLf & "Signature line #2"
strbody = ""                                                        'This is useless
strbody = strbody & rngT.Offset(x - 1).Value & l                    'This append the signature
strbody = strbody                                                   'This is useless

Please use tags to make your code more readable: select the code, press the VBA icon

 

[PhysicsGeek2022]

Ok thank you. I'm a novice at this. I'm teaching myself. This needs to get done and surprisingly there is no one in my network that can do it. I've gotten myself this far before I got stuck and need to reach out for help. So I really appreciate you.