Java - Generate a excel using java and create a macro in the excel
I have a java code that takes data from MongoDB and later creates an excel(.xls) containing this data using Apache POI but in a formatted manner.
My final requirement is to mail the last worksheet inside the excel sheet to a set of mail ID's. I cannot use the Java mail API to do that since the SMTP details of the mailbox will not be provided to me. As of now I am planning to create a macro inside the generated excel to send the data. The macro I have created to send mail is:
Sub Send_Selection_Or_ActiveSheet_with_MailEnvelope() 'Working in Excel 2002-2016 Dim Sendrng As Range On Error GoTo StopMacro With Application .ScreenUpdating = False .EnableEvents = False End With 'Note: if the selection is one cell it will send the whole worksheet Set Sendrng = Selection 'Create the mail and send it With Sendrng ActiveWorkbook.EnvelopeVisible = True With .Parent.MailEnvelope ' Set the optional introduction field thats adds ' some header text to the email body. .Introduction = "This is a test mail." With .Item .To = "[email protected]" .Subject = "My subject" .Send End With End With End With StopMacro: With Application .ScreenUpdating = True .EnableEvents = True End With ActiveWorkbook.EnvelopeVisible = False End Sub
But I don't know how to create this macro using java.
There are 1 suggested solutions here and each one has been listed below with a detailed description. The following topics have been covered briefly such as Java, Excel, Outlook, Vba, Apache Poi. These have been categorized in sections for a clear and precise explanation.