1

I'm using an Excel spreadsheet as a business form that contains a macro to assign the next sequential number.

This macro saves the form with a new file name that corresponds to the sequential number (new file is saved in the same folder as previous files), clears the contents of the active form and then assigns the next sequential number to the form for additional use.

I want to add code that will attach the last saved form in an Outlook email and place the code within the same macro and command button.

Below is the code I am using:

Sub NextRequest()
    Range("D3").Value = Range("D3") + 1
    Range("B4:B6").ClearContents
    Range("G4:G7").ClearContents
    Range("B11:B20").ClearContents
    Range("B24:b25").ClearContents
    Range("B27:G33").ClearContents
    Range("D35").ClearContents
    Range("f35").ClearContents
End Sub

Sub SaveInvReqWithNewName()
    Dim NewFN As Variant
' Copy Invoice Request to a new workbook
    ActiveSheet.Copy
    NewFN = "M:\Accounting\InvoiceRequests\InvRequest" & Range("C3") & Range("D3").Value & ".xlsx"
    ActiveWorkbook.SaveAs NewFN, FileFormat:=xlOpenXMLWorkbook
    ActiveWorkbook.Close
    NextRequest
End Sub

What code do I use to attach the last saved file to an Outlook email, and where in the macro string do I place this code?

Grant Miller
  • 27,532
  • 16
  • 147
  • 165
Gary Blankenhorn
  • 11
  • 1
  • It would be useful to see the code that you have tried. You may find a solution to your answer in this post: https://stackoverflow.com/questions/37302602/sending-email-attachments-from-excel-via-vba – 5202456 Aug 30 '18 at 21:14
  • It sounds like you're recreating built-in functionality. Custom Forms can be designed, completed, submitted, compiled, summarized, etc right in Outlook (one resource is [here](https://www.outlook-tips.net/beginner-user/using-outlook-forms-designer/)), There's also countless ready-to-use forms available for download from various sites.. – ashleedawg Aug 30 '18 at 21:47
  • Also, the code included is irrelevant to the question. Check out the [tour] as well as "[ask]" and **how to create a [mcve]**. Stack Overflow is for asking a *specific* question about a coding issue, See what's on-topic in [help/on-topic]. – ashleedawg Aug 30 '18 at 21:55

0 Answers0