excel与outlook交互无法正常发送邮件

夜行kemp

见如下代码，.To = .To & MyCell.Value & ";"  ，这句里面的分号不显示，导致最后邮箱名都连在一起了，无法发送。见下图
如果换成其他符号的 话就可以正常显示。

1. Sub Macro86()
   
2. 
   
3. 'Step 1:  Declare your variables
   
4.     Dim OLApp As Outlook.Application
   
5.     Dim OLMail As Object
   
6.     Dim MyCell As Range
   
7.     Dim MyContacts As Range
   
8.    
   
9. 'Step 2:  Define the range to loop through
   
10.     Set MyContacts = Sheets("Contact List").Range("H2:H21")
    
11.    
    
12. 'Step 3:  Open Outlook
    
13.     Set OLApp = New Outlook.Application
    
14.     Set OLMail = OLApp.CreateItem(0)
    
15.     OLApp.Session.Logon
    
16.    
    
17. 'Step 4:  Add each address in the contact list
    
18.     With OLMail
    
19.         .To = ""
    
20.           For Each MyCell In MyContacts
    
21.             .To = .To & MyCell.Value & ";"
    
22.           Next MyCell
    
23.         .BCC = ""
    
24.         .Subject = "Chapter 11 Sample Email"
    
25.         .Body = "Sample file is attached"
    
26.         .Attachments.Add ActiveWorkbook.FullName
    
27.         .Display  
    
28.     End With
    
29.    
    
30. 'Step 5:  Memory cleanup
    
31.     Set OLMail = Nothing
    
32.     Set OLApp = Nothing
    
33.    
    
34.    
    
35. End Sub

复制代码

蓝桥玄霜

For Each MyCell In MyContacts
            .To = .To & MyCell.Value & ";"
          Next MyCell
改为：
For Each MyCell In MyContacts
        ss = ss & MyCell.Value & ";"
Next MyCell
.to=left(ss,len(ss)-1)
试试看

夜行kemp

蓝桥玄霜 发表于 2020-1-13 17:14
For Each MyCell In MyContacts
            .To = .To & MyCell.Value & ";"
          Next MyCell

问题已解决，感谢老师