Search code examples
emailgoogle-apps-scriptoutlook

Sending outlook company group mail using Google script

function sendEmail(){
        var ss = SpreadsheetApp.getActiveSpreadsheet();
        var spreadsheetId = ss.getId();
        var sheet = ss.getSheetByName("sheet33");
        var range = sheet.getRange("A1:K");
        var tempSheet = ss.insertSheet(`sheet33_temp`);
        range.copyTo(tempSheet.getRange("A1"), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
        range.copyTo(tempSheet.getRange("A1"), SpreadsheetApp.CopyPasteType.PASTE_COLUMN_WIDTHS, false);
        range.copyFormatToRange(tempSheet, 1, 1, 1, 1);
        SpreadsheetApp.flush();
        var tempSheetId = tempSheet.getSheetId();
        var spreadsheetName = tempSheet.getSheetName();
        
        var url = "https://docs.google.com/spreadsheets/d/" + spreadsheetId + "/export?format=xlsx&gid=" + tempSheetId ;
                
        // dd
        var date = new Date();
        date.setDate(date.getDate() + 1);
        var dd = Utilities.formatDate(date , "GMT+9", "yyyyMMdd") ;
        
        
        var token = ScriptApp.getOAuthToken();
        var response = UrlFetchApp.fetch(url, {
          headers: {
            'Authorization': 'Bearer ' +  token  
          },
          muteHttpExceptions: true   
        });

        
        var blobs = response.getBlob();
        blobs.setName(`${spreadsheetName}_${dd}.xlsx`)
        var folder = DriveApp.getFolderById('123456asdfK');
        backupFile = folder.createFile(blobs);
                              
        
        var receipient = `[email protected]`
        var subject = spreadsheetName + `_${dd} List Share.
        var message = spreadsheetName + `_${dd} List Share.
Thanks.`

        MailApp.sendEmail(receipient, subject, message, {attachments: blobs});
        
        Browser.msgBox(`${spreadsheetName}${dd} ${receipient} Send Complete.`, Browser.Buttons.OK);


        ss.deleteSheet(tempSheet); 
}

Using a script to talk about the day's deadline in the company Copy the desired tab from the spreadsheet to a temp file, extract the desired range again, convert it to Excel, and upload it to Google Drive. In addition, an e-mail is sent with Excel as an attachment.

The problem works fine if I test it by sending it to my personal email address within the company. However, it should be sent and received to a specific Outlook mail "group" in which several people belong to the company, but the script works, but the mail is not received from the "group".

For the part that is not received from G-suit account to Outlook, I set Edit> Allow external mail reception in the group setting, but it is still not received.

When sending with a MailApp script, you cannot check the history in the mailbox sent to Outlook. Received mail is also sent to G-suit account. I want to check Gmail history, but access is restricted due to company security policy.

What you need help with

  1. How to send mail from Google Script to Outlook OR
  2. How to send mail to the Outlook "group" used by the company

In fact, if it can be solved with number 1, I think there is a possibility that it will be solved because my account uses Outlook (MS account).

However, it is too difficult to solve with my insufficient skills, so I ask for help.

I searched all of the stack / developers, but No corresponding conference was found.

I need help. Thanks in advance.

Solution

  • According to the MailApp documentation:

    Sends email.

    This service allows users to send emails with complete control over the content of the email. Unlike GmailApp, MailApp's sole purpose is sending email. MailApp cannot access a user's Gmail inbox.

    Therefore, if you want to send an email to an Outlook address using Apps Script, MailApp is the appropriate choice in this situation.

    As for receiving the email, you might want to double check the email address you inputted as well as the specific settings for the group.

    Reference