VBA中的多个收件人邮件不匹配 [英] multiple recipients email mismatch in VBA

问题描述

我正在尝试添加多个单元格上的收件人电子邮件。

我可以在工作表中选择电子邮件的范围。 p>

但是，我不断得到这个不匹配错误，我不知道如何解决它。

我一直在寻找解决方案，并采取了相同的步骤。

请原谅我，我是VBA的新人。
我非常感谢你的帮助。
我的代码在下面，

  Private Sub CommandButton1_Click（）
 
 Dim olapp As Object 
 Dim olmail As Object 
 Dim recip As String 
 
 
 lastr = ThisWorkbook.Sheets（Sheet1）。Cells（Rows.Count，1）.End xlUp）.Row 
'这是为了在身体上复制的数据范围，但还没有做到这一点
 
 lastr2 = ThisWorkbook.Sheets（Sheet1）。 .Count，7）.End（xlUp）.Row 
 
 
 recip = ThisWorkbook.Sheets（Sheet1）。Range（G3：G& lastr2）.Value 
'不匹配此步骤
 
设置olapp = CreateObject（Outlook.Application）
 
设置olmail = olapp.CreateItem（0）
 
与MItem 
 .to = recip 
 .Subject =hello
 .Body =whats up
 .display 
结束与
  

任何想法为什么会这样？

解决方

您正在试图分配一个数组（一个范围的多个小区的是Array）为字符串变量。没有测试，我知道可以用$ code> For Each 循环来解决这个问题，正如Jaycal的评论所示：

  Dim cl as Range 
对于ThisWorkbook.Sheets（Sheet1）中的每个cl，范围（G3：G& lastr2）.Cells 
 recip = recip& ; &安培; cl.Value 
下一个
  

但是您可以通过使用 string 加入功能。 Join 函数有效地对字符串数组执行此循环，因此可以节省您不必要的循环。我修改为使用范围变量的可读性：

  Dim sendRange为Range 
设置sendRange = ThisWorkbook.Sheets（ （G3：G& lastr2）
 recip = Join（Application.Transpose（sendRange.Value），;）
  

无论您使用哪种方法，您都可以使用相同的与块。

 使用MItem 
 .to = recip 
 .Subject =hello
 .Body =whats up
 .display 
结束
  

I am trying to add multiple recipients' emails that are on a range of cells.

I am able to select the range of emails on the sheet.

However, I kept getting this mismatch error and I have no idea how to solve it.

I have been looking around for solutions and did the same steps.

Please pardon me, i am new to VBA. I would very much appreciate your help. My code is below,

    Private Sub CommandButton1_Click()

        Dim olapp As Object
        Dim olmail As Object
        Dim recip As String


    lastr = ThisWorkbook.Sheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Row 
    'this is for the range of data to be copied on the body but have yet to do it

    lastr2 = ThisWorkbook.Sheets("Sheet1").Cells(Rows.Count, 7).End(xlUp).Row


        recip = ThisWorkbook.Sheets("Sheet1").Range("G3:G" & lastr2).Value 
        'mismatch after this step

        Set olapp = CreateObject("Outlook.Application")

        Set olmail = olapp.CreateItem(0)

        With MItem
         .to = recip
         .Subject = "hello"
         .Body = "whats up"
         .display
         End With

Any idea why is this happening?

解决方案

You're trying to assign an array (a range of multiple cells is an Array) to a string variable. WIthout testing, I know you can resolve this with a For Each loop, as Jaycal's comment suggested:

Dim cl as Range
For each cl in ThisWorkbook.Sheets("Sheet1").Range("G3:G" & lastr2).Cells
    recip = recip & ";" & cl.Value    
Next

But you could simplify by using the string Join function. The Join function effectively performs this loop on an array of strings, so it saves you an unnecessary loop. I modify to use a range variable for legibility:

Dim sendRange as Range
Set sendRange = ThisWorkbook.Sheets("Sheet1").Range("G3:G" & lastr2)
recip = Join(Application.Transpose(sendRange.Value), ";")

Whichever method you use, you will be able to use the same With block.

    With MItem
     .to = recip
     .Subject = "hello"
     .Body = "whats up"
     .display
     End With

这篇关于VBA中的多个收件人邮件不匹配的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！