Good morning guys, I had thought this was going to be easy but have spent hours trying to find a suitable example to tinker with and can't find something that does what I need, or figure out how to make it work...
What I am hoping for is a macro that will,
1. Prompt a user to select a location to save the file
2. Create a new CSV file based on a predefined name + Date in a "yyyymmdd" format.
3. Attach that CSV file to an email address
4. Initially I need the macro to simply open the email with attachment for review and manually send... After a while of it working and being proven I'd like to be able to make it auto send.
5. Delete the CSV file off the desktop or other chosen location from step 1.
At this stage, I have step 1 and 2. I just can't figure out how to get 3-5 working..
Appreciate any advice!
What I am hoping for is a macro that will,
1. Prompt a user to select a location to save the file
2. Create a new CSV file based on a predefined name + Date in a "yyyymmdd" format.
3. Attach that CSV file to an email address
4. Initially I need the macro to simply open the email with attachment for review and manually send... After a while of it working and being proven I'd like to be able to make it auto send.
5. Delete the CSV file off the desktop or other chosen location from step 1.
At this stage, I have step 1 and 2. I just can't figure out how to get 3-5 working..
Appreciate any advice!
Code:
Sub Export()
Dim MyPath As String
Dim MyFileName As String
MyFileName = "Non Recuring" & " " & Format(Date, "yyyymmdd")
'alternative name formatting: MyFileName = "Non Recuring" & Sheets("Sheet1").Range("B1").Value & "_" & Format(Date, "ddmmyyyy")
If Not Right(MyFileName, 4) = ".csv" Then MyFileName = MyFileName & ".csv"
Sheets("Sheet1").Copy
With Application.FileDialog(msoFileDialogFolderPicker)
.Title = "Select a Folder"
.AllowMultiSelect = False
.InitialFileName = "" '<~~ The start folder path for the file picker.
If .Show <> -1 Then GoTo NextCode
MyPath = .SelectedItems(1) & "\"
End With
NextCode:
With ActiveWorkbook
.SaveAs Filename:=MyPath & MyFileName, FileFormat:=xlCSV, CreateBackup:=False
.Close False
End With
End Sub