[Excel VBA] 선택열 각 행 값으로 메일 작성하기

Sub MAILTO()
    
 '변수 지정
    Dim rngCell             As Range
    Dim intCount            As Integer
    Dim shtSheet            As Worksheet
    Dim msg As String
    Dim rngDays             As String ' 작업일
    Dim rngNumber           As String    ' 티켓번호
    Dim rngStartTime        As String    ' 시작시간
    Dim rngEndDate          As String    ' 종료날짜
    Dim rngEndTime          As String    ' 종료시간
    Dim rngProgress         As String    ' 상태
    Dim rngName             As String    ' 요청자
    Dim rngNames            As String    ' 작업자
    Dim rngWork             As String    ' 내용
   
    Application.DisplayAlerts = False
    Application.ScreenUpdating = False

    '선택한 열의 각 행값들을 변수에 저장
 rngDays = Cells(ActiveCell.Row, 2).Value       ' 작업일
    rngNumber = Cells(ActiveCell.Row, 3).Value     ' 티켓번호
    rngStartTime = Cells(ActiveCell.Row, 4).Value   ' 시작시간
    rngEndDate = Cells(ActiveCell.Row, 5).Value   ' 종료날짜
    rngEndTime = Cells(ActiveCell.Row, 6).Value   ' 종료시간
    rngProgress = Cells(ActiveCell.Row, 7).Value   ' 상태
    rngName = Cells(ActiveCell.Row, 8).Value   ' 요청자
    rngNames = Cells(ActiveCell.Row, 9).Value   ' 작업자
    rngWork = Cells(ActiveCell.Row, 10).Value   ' 내용
       
    Dim ol As New Outlook.Application
    Dim MAIL As MailItem
   
    Set MAIL = ol.CreateItem(olMailItem)
 
 '메일 내용을 HTML 형식으로 작성한다. (위의 변수들은 여기에서 사용)
 ' _ 표시는 이어쓴다는 표시이다. 따라서 _ 이전은 엔터가 없다.
 MAIL.HTMLBody = _
        "<table class=MsoTableGrid border=1 cellspacing=0 cellpadding=0 style=" & "'" & "border-collapse:collapse;border:none;mso-border-alt:solid windowtext .5pt;mso-yfti-tbllook:1184;mso-padding-alt:0cm 5.4pt 0cm 5.4pt" & "'" & ">" & _
        "<tr style=" & "'" & "mso-yfti-irow:0;mso-yfti-firstrow:yes" & "'" & ">" & _
        "<td width=80 valign=top style=" & "'" & "width:80.6pt;border:solid windowtext 1.0pt; mso-border-alt:solid windowtext .5pt;padding:0cm 5.4pt 0cm 5.4pt" & "'" & ">티켓번호</td>" & _
        "<td width=500 valign=top style=" & "'" & "width:500.6pt;border:solid windowtext 1.0pt;border-left:none;mso-border-left-alt:solid windowtext .5pt;mso-border-alt:solid windowtext .5pt;padding:0cm 5.4pt 0cm 5.4pt" & "'" & ">" & rngNumber & "</td></tr>" & _
        "<tr style=" & "'" & "mso-yfti-irow:0;mso-yfti-firstrow:yes" & "'" & ">" & _
        "<td width=80 valign=top style=" & "'" & "width:80.6pt;border:solid windowtext 1.0pt; mso-border-alt:solid windowtext .5pt;padding:0cm 5.4pt 0cm 5.4pt" & "'" & ">요 청 자</td>" & _
        "<td width=500 valign=top style=" & "'" & "width:500.6pt;border:solid windowtext 1.0pt;border-left:none;mso-border-left-alt:solid windowtext .5pt;mso-border-alt:solid windowtext .5pt;padding:0cm 5.4pt 0cm 5.4pt" & "'" & ">" & rngName & "</td></tr>" & _
        "<tr style=" & "'" & "mso-yfti-irow:0;mso-yfti-firstrow:yes" & "'" & ">" & _
        "<td width=80 valign=top style=" & "'" & "width:80.6pt;border:solid windowtext 1.0pt; mso-border-alt:solid windowtext .5pt;padding:0cm 5.4pt 0cm 5.4pt" & "'" & ">시작시간</td>" & _
        "<td width=500 valign=top style=" & "'" & "width:500.6pt;border:solid windowtext 1.0pt;border-left:none;mso-border-left-alt:solid windowtext .5pt;mso-border-alt:solid windowtext .5pt;padding:0cm 5.4pt 0cm 5.4pt" & "'" & ">" & Format(rngDays, "YYYY/MM/DD") & Format(rngStartTime, " - HH:MM") & "</td></tr>" & _
        "<tr style=" & "'" & "mso-yfti-irow:0;mso-yfti-firstrow:yes" & "'" & ">" & _
        "<td width=80 valign=top style=" & "'" & "width:80.6pt;border:solid windowtext 1.0pt; mso-border-alt:solid windowtext .5pt;padding:0cm 5.4pt 0cm 5.4pt" & "'" & ">종료시간</td>" & _
        "<td width=500 valign=top style=" & "'" & "width:500.6pt;border:solid windowtext 1.0pt;border-left:none;mso-border-left-alt:solid windowtext .5pt;mso-border-alt:solid windowtext .5pt;padding:0cm 5.4pt 0cm 5.4pt" & "'" & ">" & Format(rngEndDate, "YYYY/MM/DD") & Format(rngEndTime, " - HH:MM") & "</td></tr>" & _
        "<tr style=" & "'" & "mso-yfti-irow:0;mso-yfti-firstrow:yes" & "'" & ">" & _
        "<td width=80 valign=top style=" & "'" & "width:80.6pt;border:solid windowtext 1.0pt; mso-border-alt:solid windowtext .5pt;padding:0cm 5.4pt 0cm 5.4pt" & "'" & ">작업내용</td>" & _
        "<td width=500 valign=top style=" & "'" & "width:500.6pt;border:solid windowtext 1.0pt;border-left:none;mso-border-left-alt:solid windowtext .5pt;mso-border-alt:solid windowtext .5pt;padding:0cm 5.4pt 0cm 5.4pt" & "'" & ">" & rngWork & "</td></tr>" & _
        "</table>"

 '받는 사람을 test@test.co.kr 참조를 test2@test.co.kr를 지정한다.
 MAIL.To = "test@test.co.kr"
 MAIL.CC = "test2@test.co.kr"

 '제목을 지정한다 (여기에서는 위 변수로 조합하였다.)
 MAIL.Subject = rngProgress & " : " & rngName & " - " & rngNumber
 MAIL.Send
 
End Sub