VBA-Save file, paste data and email to a user-Almost there, just need a little more

[MikeyW1969]

Hi Guys,
I posted a question earlier about how to save with a prompted name and the date appended, and I got that answered. I also have my code set to select ONLY the rows filled and paste them into the body of an email, and send that email. I also want to attach the current file. THis makes it so my recipient can see the relevant data(It's just a supplies ordering form I created) right in the body of his email, but he also has a file to save for later, but I can't get it to attach the file. I'm sure that it's because I'm piecing this all together from different sources, but it's the big sticking point.

Here is my code:

Sub SaveAndEmail()
  ' Prompts for name, appends date and saves file as NON Macr-enabled file
  ActiveWorkbook.SaveAs ("C:\Users\ThisIsMe\Documents\Supplies and Parts Odering\" & InputBox("Filename?") & "_" & Month(Now()) & "_" & Day(Now()) & "_" & Year(Now()) & ".xlsm")
  
  'Selects only rows with data
  Range("A1").Resize(Cells.Find(What:="*", SearchOrder:=xlRows, _
      SearchDirection:=xlPrevious, LookIn:=xlValues).Row, _
      Cells.Find(What:="*", SearchOrder:=xlByColumns, _
      SearchDirection:=xlPrevious, LookIn:=xlValues).Column).Select
      
    ' Show the envelope on the ActiveWorkbook.
   ActiveWorkbook.EnvelopeVisible = True
   
   ' Set the optional introduction field thats adds
   ' some header text to the email body. It also sets
   ' the To and Subject lines. Finally the message
   ' is sent.
   With ActiveSheet.MailEnvelope
      .Introduction = "This is a sample worksheet."
      .Item.To = "emailaddress@gmail.com"
      .Item.Subject = "My subject"
      .Attachments.Add (ActiveWorkbook.FullName)
      .Item.Send
   End With
      ActiveWorkbook.Close SaveChanges:=True
End Sub

Everything works fine, except that I get an error that is definitely attached to the .Attachments.Add (ActiveWorkbook.FullName) line, because if I comment that line out, it works fine. The error is Run-time error '438' 'Object doesn't support this property or method'.

The final piece of the puzzle is getting it to let me save the final workbook as a non-macro file. This isn't critical, but if my purchaser doesn't have to click 'Enable Macros', it would be better. Right now, it saves as .xslm just fine, but if I change that extension to .xslm, it gives me an error. Is this fixable, or am I doing that part wrong? I basically want my current file to have the macros so that I can run this script, but don't need any macros in the final one that I email out.


As always, thanks for the help, this is a great community!!

 

[Dryver14]

I am not sure why you can't save the file as .xlsx.

With regard to the attachment, you are trying to send the wrong thing.

If it were me I would use activeworkbook.path &

and then set the remaining part of the address to a variable
so this part InputBox("Filename?") & "_" & Month(Now()) & "_" & Day(Now()) & "_" & Year(Now()) & ".xlsm")

I would set to a variable dim FN as string then set FN = InputBox("Filename?") & "_" & Month(Now()) & "_" & Day(Now()) & "_" & Year(Now()) & ".xlsx")

so this line .Attachments.Add (ActiveWorkbook.FullName) becomes .Attachments.Add Activeworkbook.path & FN

 

[MikeyW1969]

So this was overwhelming at first, I was getting confused. After sitting on it for the night, I think I figured it out.

Instead of just attaching "The currently open document", you're suggesting that I create a variable that basically points to the file that I just saved, so the whole thing points right to the location that I had defined earlier when I saved the file the first time(As well as using the name I gave the file), correct? I was getting confused because I thought that you were also suggesting how to redo the 'Save' part and define the location.

I'll try this. Thanks!