Attach into email with specific filename

imskin

New Member
Joined
Mar 11, 2013
Messages
21
Hi,

May i know what went wrong with my code?I want to attach file with name "ITST01" in the folder which is defined by DialogFolderPicker.But there is error return stated cannot find the file, please ensure the path is correct.

Code:
Sub SendEmail()
Dim Fpath As String
Dim diaFolder As FileDialog
Dim strfolder As String


Set diaFolder = Application.FileDialog(msoFileDialogFolderPicker)
diaFolder.AllowMultiSelect = False
diaFolder.Show


strfolder = diaFolder.SelectedItems(1)


'Mth = InputBox("Please insert Month, MMYY")
'DueDate = InputBox("Please insert due date")

Call Test


End Sub


Sub Test()
Dim EmailSubject As String
Dim SendTo As String
Dim EmailBody As String
Dim ccTo As String
Dim strpath As String




EmailSubject = "HC allocation_Test Engineer" & "_" & Mth


strpath = Dir(strfolder & "1TST01.xlsx")

SendTo = "name@yahoo.com" ' Use name of distribution list

EmailBody = "Hi," & vbCrLf & vbCrLf & "Kindly review HC allocation file and update the allocation % if there is any changes" & vbCrLf & vbCrLf & "Due Date: " & DueDate & vbCrLf & vbCrLf & "Rgds, SK"

Set App = CreateObject("Outlook.Application")
Set Itm = App.CreateItem(0)

With Itm
.Subject = EmailSubject
.To = SendTo
.Body = EmailBody
.Attachments.Add (strpath)
.Display


End With

Set App = Nothing
Set Itm = Nothing
End Sub
Code:
 

Some videos you may like

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
Because you have declared strfolder within the procedure SendEmail it is visible only to that procedure, and can't be accessed by procedure Test. Either declare the function at the top of the module (and nowhere else) or combine the two procedures.
 

imskin

New Member
Joined
Mar 11, 2013
Messages
21
:p
Thanks a lot. It solved my problem by combining the code. It cracked my head to figure out what the problem as i am not really familiar with VBA.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,216
Messages
5,594,886
Members
413,947
Latest member
gizmolucy

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top