LibreCalc send an email with html formatting, clickable links and Attachments to multiple people using Thunderbird

Question

If you are using Linux, LibreCalc and thunderbird and need to send an email this might help you

• Add an attachment
• Specify multiple Recipients from Cell input
• Send the email through Thunderbird without issues
• Have a larger formatted body to the email
• Specify CC(Carbon Copy) and BCC(Blind Carbon Copy)

The code below has instructions notated throughout to follow. Just copy the code and edit your LibreCalc sheet and the Macro code as needed.

Answer 1

REM  *****  BASIC  *****
Sub sendEmailWithAtachmentsAndFormatting

'Set variants to be used in the email
Dim oSheet1 As Variant
Dim oRangeUser As String ' Data of range, all cells of all rows in range 
Dim oRangeClickableLinkFormattedInCalc As Variant ' Data of range, all cells of all rows in range 

'Specifying the Calc sheet to be referenced. 
'Replace the name FORM with whatever your Sheet name is; I have a Form setup to pull information from, so that's what I use
    oSheet1 = ThisComponent.Sheets.getByName("FORM") 'Selects the FORM tab as the place to pull info from'

'Getting the email addresses(recepients) you want to send to
'Replace the Cells listed in the code with the ones you want referenced
    oRangeUser1 =  oSheet1.getCellRangeByName("K2").string 'Email Address 1'
    oRangeUser2 =  oSheet1.getCellRangeByName("K3").string 'Email Address 2'
    oRangeUser3 =  oSheet1.getCellRangeByName("K4").string 'Email Address 3'
    oRangeUser4 =  oSheet1.getCellRangeByName("K5").string 'Email Address 4'
    oRangeUser5 =  oSheet1.getCellRangeByName("K6").string 'Email Address 5'
    oRangeUser6 =  oSheet1.getCellRangeByName("K7").string 'Email Address 6'
    oRangeUser7 =  oSheet1.getCellRangeByName("K8").string 'Email Address 7'
    oRangeUser8 =  oSheet1.getCellRangeByName("K9").string 'Email Address 8'
    oRangeUser9 =  oSheet1.getCellRangeByName("K10").string 'Email Address 9'
    oRangeUser10 =  oSheet1.getCellRangeByName("K11").string 'Email Address 10'
  
 'Getting the HTML link you want to use
 'NOTE: you will need to forma the link in the cell for it to be used in the email properly <a href="LinkURL" target="_blank">LinkText</a>
    oRangeClickableLinkFormattedInCalc = oSheet1.getCellRangeByName("M1").string 'Specifies a blank template link with new parent ticket'

'Combining the emails(recepients) that the message is being sent to 
'NOTE: The only way i could get Thunderbird to recognize seperate emails was using this format &";"& oRangeUser &";"& 
'            For somereason it only works with the ";" in the middle of each email
     eMailAddress = oRangeUser1 &";"& oRangeUser2 &";"& oRangeUser3 &";"& oRangeUser4 &";"& oRangeUser5 &";"& oRangeUser6 &";"& oRangeUser7 &";"& oRangeUser8 &";"& oRangeUser9 &";"& oRangeUser10

    
'Specify email Subject
       eSubject = "This is my email Subject Text"
       
'Specify email Body
' Your text must be contained within quotation marks: "Text here"
'If you want to make it easier to read your code, add an underscore at the end of your text and then write the next line of code below using & to connect the text, repeat as nessisary
'To combine strings of text use &, to combine Text with a Variable(link text etc) use "text"& varientName &"Text" Ensure that there is a space before and after the varient
'If you want to use special characters they must be seperate form the actual text for the system to recognize it
' colon(:) is &#58;   |   comma(,) is &#x2c;
       eBody =  "Start of Your Email<br><br>" _
       &"The br twice indicates you want a space between lines<br>" _
       &"One br indicates next line" _
       &"<ul>"_
         &"<li>UnOrderedListOne</li>"_
         &"<li>UnOrderedListTwo</li>"_
       &"</ul>"_
       &"<ol>"_
         &"<li>OrderedListOne</li>"_
         &"<li>OrderedListTwo</li>"_
       &"</ol>"_
       &"<b>SomeBoldText</b> <i>SomeItalicizedText</i>"_
       &"Displaying a plain URL not linked - htttps"&"&#58;"&"//URLtext"_
       &"Text before clickable link"& oRangeClickableLinkFormattedInCalc &"Text after the clickable link" _
       &"The final line of your body dosnt need the underscore"
       
'Specify email setup informaiton
       eMailer = createUnoService ("com.sun.star.system.SimpleCommandMail")
       eMailClient = eMailer.QuerySimpleMailClient()
       eMessage = eMailClient.createSimpleMailMessage()
 'Specify Recipient(s) using information form FORM 
       eMessage.Recipient = eMailAddress 
 'Specify Carbon Copy Recipient(s) usingStatic text 
       eMessage.CcRecipient = Array("NAME@email.com")
 'Specify BlindCarbon Copy Recipient(s) usingStatic text 
 'NOTE: I have this removed as its not needed for me
 '      eMessage.BccRecipient = Array("NAME@email.com") 
       eMessage.Subject = eSubject
       eMessage.Body = eBody
'Specify where email attachment is coming from
       AttachmentURL = convertToUrl("home/YourProfileName/Documents/DocName.pdf")
'Adding email attachment
       eMessage.Attachement = Array(AttachmentURL)
       eMailClient.sendSimpleMailMessage ( eMessage, com.sun.star.system.SimpleMailClientFlags.NO_USER_INTERFACE )
    End Sub