cdo email attachment

[Dave199]

Hello All,

I would like to than everyone in advance as the information I have gathered and things I have learnt from the Mr Excel forums make me look like a genius at work.
I now have a problem with trying to attached a file to an email using vba and cdo.
When I type the file location into the code it works fine, when I try and link a cell with the file location to the code it wont work.
Thanks again in advance.
Dave

Sub CommandButton10_Click()
Dim Mail As New Message
Dim Config As Configuration
Set Config = Mail.Configuration
Dim cell As Range
Dim strbody As String
For Each cell In Sheets("Time Card Data").Range("ah2:ah6")
strbody = strbody & cell.Value & vbNewLine
Next


Config(cdoSendUsingMethod) = cdoSendUsingPort
Config(cdoSMTPServer) = "smtp.gmail.com"
Config(cdoSMTPServerPort) = 25
Config(cdoSMTPAuthenticate) = cdoBasic
Config(cdoSMTPUseSSL) = True
Config(cdoSendUserName) = "excel.email.cel@gmail.com"
Config(cdoSendPassword) = "celmec2014"
Config.Fields.Update


Mail.To = Sheets("Time Card Data").Range("v20").Value
Mail.From = Config(cdoSendUserName)
Mail.Subject = Sheets("Time Card Data").Range("ah7").Value
Mail.TextBody = strbody
Mail.AddAttachment = Sheets("Time Card Data").Range("ah10").Value


On Error Resume Next


Mail.Send


If Err.Number <> 0 Then
MsgBox Err.Description, vbCritical, "There was an error"
Exit Sub
End If
MsgBox "Your email has been send!", vbInformation, "Sent"


End Sub

 

[Dave199]

I thought this might be useful, this is the contents of the cell I am trying to link to,

"file:///Z:\Time%20Sheets\CSV%20Files\Celmec%20Timesheet%20Factory%20WE%2002-12-14.csv" when I paste that into the code it works fine.

<tbody>
</tbody>