Excel VBA - Как я могу отправить электронное письмо с текстом, относящимся к определенным значениям ячеек?

Основываясь на вашем объяснении, я разработал метод VBA в Excel.

Это поможет вам сгенерировать конкатенированную строку, которую вы хотите.

Скопируйте и вставьте ниже код Excel VBA в редакторе и попробуйте запустить его.

Sub Generate_String()
Dim HTML_body As String
Dim count_var As Integer
Dim arr() As String
Dim arr_val() As Integer
Dim great_count As Integer
Dim arr_now As Integer
Dim xMailBody As String
Dim xOutApp As Object
Dim xOutMail As Object

Set xOutApp = CreateObject("Outlook.Application")
Set xOutMail = xOutApp.CreateItem(0)

arr_now = 0

'Gets the count of number of zeroes available in the B3 to last data filled row of that column.
great_count = Application.WorksheetFunction.CountIf(Range("B3:B" & Range("B3").End(Excel.XlDirection.xlDown).Row), ">0")

HTML_body = "Hi <Name>," & vbnewline & vbnewline & "I'd like to order"

'If orders are there then will go for further processing.
If great_count <> 0 Then

    'Resizes array according to the count
    ReDim arr(0 To great_count - 1)
    ReDim arr_val(0 To great_count - 1)

    'Loops through the range and input product into the arr and it's value in arr_val
    If great_count > 0 Then
        For i = 3 To Range("B3").End(Excel.XlDirection.xlDown).Row
            If Range("B" & i).Value > 0 Then
                arr(arr_now) = Range("A" & i).Value
                arr_val(arr_now) = Range("B" & i).Value
                arr_now = arr_now + 1
            End If
        Next i
    End If

    'Looping through each element in the array to get the desired result.
        If great_count = 1 Then
            HTML_body = HTML_body + " " + CStr(arr_val(j)) + " of " + arr(j)
        Else
            For j = 0 To UBound(arr)
                If j = 0 Then
                    HTML_body = HTML_body + " " + CStr(arr_val(j)) + " of " + arr(j)
                ElseIf j = UBound(arr) Then
                    HTML_body = HTML_body + " & " + CStr(arr_val(j)) + " of " + arr(j)
                Else
                    HTML_body = HTML_body + " , " + CStr(arr_val(j)) + " of " + arr(j)
                End If
            Next j
        End If
Else
    HTML_body = "No Orders"
End If

HTML_body = HTML_body & vbnewline & "Kind Regards" & vbnewline & "<Name>"
With xOutMail
        .To = "[email protected]"
        .CC = ""
        .BCC = ""
        .Subject = "Test email send by button clicking"
        .Body = HTML_body
        .Display   'or use .Send
End With
End Sub

Измените его в соответствии с вашими потребностями. Надеюсь, я смог вам помочь.

Я отредактировал код в соответствии с вашими потребностями. Проблема заключалась в том, что вы добавляли текст «С уважением» перед обработкой.

EDIT2:

С проверкой проверяет, является ли столбец суммы для любой строки пустым

Sub Generate_String()
Dim HTML_body As String
Dim count_var As Integer
Dim arr() As String
Dim arr_val() As Integer
Dim great_count As Integer
Dim arr_now As Integer
Dim rng_Body As Range
Dim xMailBody As String
Dim xOutApp As Object
Dim xOutMail As Object
Dim validate_pass As Boolean

'Checks if the any of the range has blank values
'if blank value is found it will make validate_pass variable to false.
Set rng_Body = Range("B3:B" & Range("B3").End(Excel.XlDirection.xlDown).Row)
validate_pass = True
For Each r In rng_Body
    If Trim(r.Text) = "" Then
        validate_pass = False
        Exit For
    End If
Next r

'If validate_pass variable is false then throws out error message.
'Else it will go through the normal procedure to sent out emails.
If validate_pass = False Then
    MsgBox "Your appropriate error message if any of the amount value found blank", vbCritical
Else
    Set xOutApp = CreateObject("Outlook.Application")
    Set xOutMail = xOutApp.CreateItem(0)

    arr_now = 0

    Set rng_Body = Range("B3:B" & Range("B3").End(Excel.XlDirection.xlDown).Row)


    'Gets the count of number of zeroes available in the B3 to last data filled row of that column.
    great_count = Application.WorksheetFunction.CountIf(Range("B3:B" & Range("B3").End(Excel.XlDirection.xlDown).Row), ">0")

    HTML_body = "Hi <Name>," & vbNewLine & vbNewLine & "I'd like to order"

    'If orders are there then will go for further processing.
    If great_count <> 0 Then

        'Resizes array according to the count
        ReDim arr(0 To great_count - 1)
        ReDim arr_val(0 To great_count - 1)

        'Loops through the range and input product into the arr and it's value in arr_val
        If great_count > 0 Then
            For i = 3 To Range("B3").End(Excel.XlDirection.xlDown).Row
                If Range("B" & i).Value > 0 Then
                    arr(arr_now) = Range("A" & i).Value
                    arr_val(arr_now) = Range("B" & i).Value
                    arr_now = arr_now + 1
                End If
            Next i
        End If

        'Looping through each element in the array to get the desired result.
            If great_count = 1 Then
                HTML_body = HTML_body + " " + CStr(arr_val(j)) + " of " + arr(j)
            Else
                For j = 0 To UBound(arr)
                    If j = 0 Then
                        HTML_body = HTML_body + " " + CStr(arr_val(j)) + " of " + arr(j)
                    ElseIf j = UBound(arr) Then
                        HTML_body = HTML_body + " & " + CStr(arr_val(j)) + " of " + arr(j)
                    Else
                        HTML_body = HTML_body + " , " + CStr(arr_val(j)) + " of " + arr(j)
                    End If
                Next j
            End If
    Else
        HTML_body = "No Orders"
    End If

    HTML_body = HTML_body & vbNewLine & "Kind Regards" & vbNewLine & "<Name>"
    With xOutMail
            .To = "[email protected]"
            .CC = ""
            .BCC = ""
            .Subject = "Test email send by button clicking"
            .Body = HTML_body
            .Display   'or use .Send
    End With
    MsgBox "Order Email has been Sent"
End If


End Sub

Ну, вы можете комбинировать текст и числа вместе, чтобы получить то, что вы хотите:

=A1&" "&A2&" "&A3

Где ячейка A1 содержит «Я бы хотел», ячейка A2 содержит значение 100, ячейка A3 содержит «переменные резисторы».

что приводит к: я хотел бы 100 переменных резисторов