[업무자동화] 다수의 회원(조합원, 기자 등)에게 각각 다른 내용으로 메일 한번에 보내기

얼마 전에 카톡에 도움을 요청하는 글이 올라왔다. 오피스 프로그램이나 간단한 소프트웨어 프로그램을 활용하여 개인정보 유출을 방지하고 개별 회원(조합원, 기자 등)들에게 각 개인에게 해당되는 내용만 넣어서 메일을 보낸다면 업무에 도움이 될 것 같아서 구글에서 제공하는 프로그램(※ 구글 드라이브(Google Drive), 구글 스프레드시트(Google Spreadsheet), 구글 앱스크립트(Apps Script))을 이용하여 '다수의 회원에게 각각 다른 내용으로 메일 한번에 보내기' 자동화 프로그램을 만들었다.

회원(조합원, 기자 등) 명단과 첨부할 파일을 구글 드라이브에 올린다. 첨부할 파일은 폴더를 따로 만든다.

첨부할 파일은 폴더를 열어서 안에 있는 모든 파일에 대한 권한을 '링크가 있는 누구나(Anyone with the link)'로 설정한다.

스프레드시트 안에 시트는 2개다. 회원(조합원, 기자 등) 명단이 있는 list와 메일 제목과 본문에 들어갈 내용이 있는 mailbody이다. 시트명을 바꾸거나 컬럼 순서를 바꾼다면 아래 코드에서 수정하면 된다. 메일 본문은 html을 사용하여 줄바꿈, 글자색, 글자 굵기 등을 지정할 수 있다.

구글 스프레드시트의 확장프로그램인 앱스크립트(Apps Script)를 연다.

코드를 작성한다. 아래에 있는 코드를 복사하여 사용할 수 있다. ※ 코드는 Yochang Song님의 유튜브를 참고하여 만들었습니다.(https://www.youtube.com/playlist?list=PLI69hfdYKNQfi15izuXvQtI-jnPWJR4fg)

=== 여기부터 코드 ===

function sendEmail() {
    const ss = SpreadsheetApp.getActiveSpreadsheet()
    const sheet1 = ss.getSheetByName('list'); // 이메일 보낼 사람들 목록이 들어간 시트명
    const sheet2 = ss.getSheetByName('mailbody'); // 이메일 내용이 들어간 시트명
    const subject = sheet2.getRange(2,1).getValue(); // 이메일 제목 추출
    const n = sheet1.getLastRow(); // 이메일 보낼 명단이 전체 몇 명인지 확인

    const folder = DriveApp.getFolderById('폴더 id'); // 첨부할 파일이 들어 있는 폴더 id를 확인해서 추가
    const files = folder.getFiles();
    const fileMap = {};
    while (files.hasNext()) {
      let file = files.next();
      // 한글 자소가 분리될 경우 다시 합친다.
      const fileName = file.getName().replace(/\s/g, '').normalize('NFC');
      const encodeFileName = Utilities.base64Encode(fileName);
      // Map을 활용해서 폴더 내 파일을 파일이름으로 분류한다.
      fileMap[encodeFileName] = file;
    }
    
    // 2번째 행부터 이메일 주소가 있기때문에 2번째 행부터 시작해서 마지막 줄까지 반복하는 구문
    for (let i = 2; i < n+1 ; i++ ) {
        // 이메일 주소는 2번째 열에 있으니까 (i,2)
        const emailAddress = sheet1.getRange(i,2).getValue(); 
        // 이름은 첫번째 열에 있으니까!! (i, 1)
        const name = sheet1.getRange(i,1).getValue();
        const juso = sheet1.getRange(i,3).getValue();
        const 주민번호 = sheet1.getRange(i,4).getValue();
        const HP = sheet1.getRange(i,5).getValue();

        // 이메일 제목에서 <name>이라고 써진 부분을 변경한다
        const updateSubject = subject.replace("<name>",name); 
        
        // 이메일 내용이 될부분 추출
        let message = sheet2.getRange(2,2).getValue();
        // <name> 이라고 써진 부분을 실제 이름으로 변경한다.
        message = message.replace("<name>",name).replace("<juso>",juso).replace("<주민번호>",주민번호).replace("<HP>",HP);

        // 보내야할 파일을 특정해서, attachments array로 만들어서 보낸다.
        const excelFileName = sheet1.getRange(i,6).getValue().replace(/\s/g, '');
        const pdfFileName = sheet1.getRange(i,7).getValue().replace(/\s/g, '');
        const encodeExcelFileName = Utilities.base64Encode(excelFileName);
        const encodePdfFileName = Utilities.base64Encode(pdfFileName);
        const excelFile = fileMap[encodeExcelFileName];
        const pdfFile = fileMap[encodePdfFileName];
        const attachments = [excelFile, pdfFile];

        // 메일 발송
        MailApp.sendEmail({
          to: emailAddress,
          subject: updateSubject,
          htmlBody: message,
          attachments: attachments,
        });
    }
}