时间戳

首页 > 解决方案 > Excel VBA：用作电子邮件地址时缺少前导零

excel - Excel VBA：用作电子邮件地址时缺少前导零

问题描述

我正在尝试使用 Excel VBA 起草一封新电子邮件，并在抄送列表中添加电子邮件地址“00000@mycompany.com”，而“00000”是 5 位数的员工编号。用户应在表格范围“T5”、“V5”或“X5”中填写员工编号。

但是，如果他们的员工编号以零开头，则电子邮件地址中将缺少前导零。

例如，他们填写的号码是“01234”，但草稿电子邮件中的电子邮件地址将是“1234@mycompany.com”

我努力了

.text
.numberformat="@" and 
.numberformat="00000"

但他们不工作。

谁能帮我解决这个问题？多谢！

Private Sub sendmail_Click()
    Dim sh As Worksheet
    Set sh = ThisWorkbook.Sheets("Form")

    Dim OA As Object
    Dim msg As Object
    Dim filepath As String
    Set OA = CreateObject("Outlook.Application")

    Dim folder
    Set folder = CreateObject("scripting.filesystemobject")

    Set msg = OA.CreateItem(0)

    With msg
    .display
    End With
    signature = msg.Body

    Dim var_cc As String
    var_cc = ""

    With msg
    .To = "abc@mycompany.com"
    If Application.WorksheetFunction.IsNumber(sh.Range("T5")) = True Then
    var_cc = var_cc & sh.Range("T5").NumberFormat = "@" & "@mycompany.com;"
    Else
    End If
    If Application.WorksheetFunction.IsNumber(sh.Range("V5")) = True Then
    var_cc = var_cc & sh.Range("V5").Value & "@mycompany.com;;"
    Else
    End If
    If Application.WorksheetFunction.IsNumber(sh.Range("X5")) = True Then
    var_cc = var_cc & sh.Range("X5").Value & "@mycompany.com;;"
    Else
    End If
    .cc = var_cc
    .Subject = sh.Range("G5").Value & " " & sh.Range("D5").Value & " " & sh.Range("A5").Value & " " & sh.Range("E3").Value & " / " & 
    sh.Range("E2").Value
    .Body = "Dear All," & vbNewLine & _
    "Please find attached missing info. Thank you." & _
     vbNewLine & signature
     .importance = 2
     .Attachments.Add (Application.ActiveWorkbook.FullName)

    End With


    With Application
        .ScreenUpdating = True
        .EnableEvents = True
    End With

End Sub

标签： excelvba

解决方案

您可以使用

var_cc = var_cc & Format(sh.Range("T5").Value, "00000") & "@mycompany.com;"

代替

var_cc = var_cc & sh.Range("T5").NumberFormat = "@" & "@mycompany.com;"

推荐阅读