Forum Discussion

karol polubinski's avatar
karol polubinski
Brass Contributor
May 20, 2018

1 VBA Buttton, Creat fold if there is not, save in folder created, send email automatically.

I am having some problems getting to work a VBA button

I have created a button called Send order, it is supposed to save only the first work sheet attach it to an email with specific address and send it automatically.

 

The excel file is called: order book

and the work sheet is called: order summery

 

The message in the Email comes on the email but in my case it all comes in one line;,

 

the button has to save in a separate folder called orders with the current date. doesn't do it.

Then attach it to an email.  The only thing it does.

The message to be sent in the email is: "Good Day to All, Please find Attached order sheet for the week. Please Acknowledge reciept of the attached document, Thank you. Best Regards, "

 

Once it has done all that it needs to be sent automatically. I does that but not all what I need it to do.

 

It doesn't create the file to save the the worksheet and it does not put the current date.

I am using the following code:

 

Private Sub CommandButton3_Click()

 

Dim sFileName As String
Dim sPath As String

CommandButton3.Enabled = False

sFileName = Format(DateValue(Now()), "mmm_dd_yyyy") & "_" & _
Format(TimeSerial(Hour(Now()), Minute(Now()), Second(Now())), "hh_mm_ss_AM/PM")
sPath = "http://sptdsrefining."
sFileName = sFileName & ".xls"
ActiveWorkbook.SaveAs Filename:=sPath & sFileName, FileFormat:=xlNormal, ReadOnlyRecommended:=False
ThisWorkbook.Close SaveChanges:=False

Dim OutlookApp As Object
Dim OutlookMail As Object
Set OutlookApp = CreateObject("Outlook.Application")
Set OutlookMail = OutlookApp.CreateItem(0)
On Error Resume Next
With OutlookMail
.To = "thesummer31@hotmail.com"
.CC = ""
.BCC = ""
.Subject = "Weekly Order Note"
.Body = "Good Day to All, Please find Attached order sheet for the week. Please Acknowledge reciept of the attached document, thank you. Best Regards, Camp Boss"
.Attachments.Add Application.ActiveWorkbook.FullName
.Send
End With
Set OutlookMail = Nothing
Set OutlookApp = Nothing
End Sub

  • I cannot get it to creat a one time only folder and save. I get errors when I run it and I don't have a clue how to debug it.

Resources