文章/答案/技术大牛

发布

社区首页 >问答首页 >跳过代码以在单元格为空的位置插入单元格值

问跳过代码以在单元格为空的位置插入单元格值
EN

Stack Overflow用户

提问于 2020-01-21 04:42:02

回答 2查看 47关注 0票数 0

我在我的工作表1代码中列出了以下内容，将单元格值移动到Outlook电子邮件的正文。

如果A列中的单元格为空，我将尝试停止为给定行插入文本。

Private Sub CommandButton1_Click()
'Create email with attachment, subject, and list of email addresses
ThisWorkbook.Save
Dim outlookApp As Object
Dim myMail As Object
Dim Source_File, to_emails, cc_emails As String
Dim file_to_send As String
Dim body_code As String
Dim i As Integer

Set outlookApp = CreateObject("Outlook.Application")
Set myMail = outlookApp.CreateItem(olMailItem)

For i = 2 To 22
    to_emails = to_emails & Cells(i, 13) & ";"
    'for CC: change the 13 to whatever column count from the left where your CC list is
    'cc_emails = cc_emails & Cells(i, 13) & ";"
Next i

Source_File = ThisWorkbook.FullName
myMail.Attachments.Add Source_File

'myMail.CC = cc_emails
myMail.To = to_emails
myMail.Subject = Range("Q2").Value & " 10-8 Form " & Format(Date, "mm/dd/yy")

myMail.Body = Range("B2") & " Shift" & "  -  " & Format(Date, "mmmm dd, yyyy") _
    & vbNewLine & vbNewLine & "Sergeant: " & Range("A6") & ", " & Range("B6") & vbNewLine & "          Status: " & Range("C6") _
    & vbNewLine & vbNewLine & "Corporal: " & Range("A8") & ", " & Range("B8") & vbNewLine & "          Status: " & Range("C8") _
    & vbNewLine & vbNewLine & "Assigned Deputies" & vbNewLine & vbNewLine & _
    Range("A10") & ", " & Range("B10") & vbNewLine & "          Assignment/Zone: " & Range("C10") & vbNewLine & _
    Range("A11") & ", " & Range("B11") & vbNewLine & "          Assignment/Zone: " & Range("C11") & vbNewLine & _
    Range("A12") & ", " & Range("B12") & vbNewLine & "          Assignment/Zone: " & Range("C12") & vbNewLine & _
    Range("A13") & ", " & Range("B13") & vbNewLine & "          Assignment/Zone: " & Range("C13") & vbNewLine & _
    Range("A14") & ", " & Range("B14") & vbNewLine & "          Assignment/Zone: " & Range("C14") & vbNewLine & _
    Range("A15") & ", " & Range("B15") & vbNewLine & "          Assignment/Zone: " & Range("C15") & vbNewLine & _
    Range("A16") & ", " & Range("B16") & vbNewLine & "          Assignment/Zone: " & Range("C16") & vbNewLine & _
    Range("A17") & ", " & Range("B17") & vbNewLine & "          Assignment/Zone: " & Range("C17") & vbNewLine & _
    Range("A18") & ", " & Range("B18") & vbNewLine & "          Assignment/Zone: " & Range("C18")

myMail.Display
ThisWorkbook.Save

End Sub

excel

vba

outlook

回答 2

Stack Overflow用户

回答已采纳

发布于 2020-01-21 04:52:51

我肯定会打破你的那堵巨大的文字墙。这可以通过一个循环来完成。

让我们在这里使用For循环。

Dim concatString as String

For i = 10 To 18
    If Not Cells(i, "A").Text = vbNullString Then
         'Add to growing string
         concatString = concatString + Cells(i, "A").Text & ", " & Cells(i, "B").Text & vbCr
         concatString = concatString + "Assignment/Zone: " & Cells(i, "C").Text & vbCr
    End If
Next i

如果A列包含一个空字符串，我们跳过它并移动到下一行。

我在你添加更多代码之前发布了这篇文章，但我想你已经明白了。分解巨大的代码块，在循环中只放一个循环通过列A、B和C。根据需要调整循环约束。

下面是它在您的代码中的样子：

'...
'your code here
'...

Dim concatString as String

For i = 10 To 18
    If Not Cells(i, "A").Text = vbNullString Then
         'Add to growing string
         concatString = concatString + Cells(i, "A").Text & ", " & Cells(i, "B").Text & vbCr
         concatString = concatString + "Assignment/Zone: " & Cells(i, "C").Text & vbCr
    End If
Next i

myMail.Body = Range("B2") & " Shift" & "  -  " & Format(Date, "mmmm dd, yyyy") _
    & vbNewLine & vbNewLine & "Sergeant: " & Range("A6") & ", " & Range("B6") & vbNewLine & "          Status: " & Range("C6") _
    & vbNewLine & vbNewLine & "Corporal: " & Range("A8") & ", " & Range("B8") & vbNewLine & "          Status: " & Range("C8") _
    & vbNewLine & vbNewLine & "Assigned Deputies" & vbNewLine & vbNewLine & concatString

我删除了所有这些多余的空格，不确定你是否真的需要它们，或者这是从VBE复制/粘贴的痕迹。

票数 0

Stack Overflow用户

发布于 2020-01-21 05:25:59

这是最终的代码，最终做到了这一点。感谢jclasley

`Private Sub CommandButton1_Click()
'Create email with attachment, subject, and list of email addresses
ThisWorkbook.Save
Dim outlookApp As Object
Dim myMail As Object
Dim Source_File, to_emails, cc_emails As String
Dim file_to_send As String
Dim i As Integer
Dim concatString As String

Set outlookApp = CreateObject("Outlook.Application")
Set myMail = outlookApp.CreateItem(olMailItem)

For i = 2 To 22
    to_emails = to_emails & Cells(i, "M") & ";"
    'for CC: change the 13 to whatever column count from the left where your CC list is
    'cc_emails = cc_emails & Cells(i, 13) & ";"
Next i

Source_File = ThisWorkbook.FullName
myMail.Attachments.Add Source_File

'myMail.CC = cc_emails
myMail.To = to_emails
myMail.Subject = Range("Q2").Value & " 10-8 Form " & Format(Date, "mm/dd/yy")

For i = 10 To 18
    If Not Cells(i, "A").Text = vbNullString Then
         'Add to growing string
         concatString = concatString + Cells(i, "A").Text & ", " & Cells(i, "B").Text & vbCr
         concatString = concatString + "Assignment/Zone: " & Cells(i, "C").Text & vbNewLine & vbCr
    End If
Next i

myMail.Body = Range("B2") & " Shift" & "  -  " & Format(Date, "mmmm dd, yyyy") _
    & vbNewLine & vbNewLine & "Sergeant: " & Range("A6") & ", " & Range("B6") & vbNewLine & "          Status: " & Range("C6") _
    & vbNewLine & vbNewLine & "Corporal: " & Range("A8") & ", " & Range("B8") & vbNewLine & "          Status: " & Range("C8") _
    & vbNewLine & vbNewLine & "Assigned Deputies" & vbNewLine & vbNewLine & concatString

myMail.Display
ThisWorkbook.Save

End Sub

enter code here

票数 0

页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持

原文链接：

https://stackoverflow.com/questions/59830574

复制

相似问题

问跳过代码以在单元格为空的位置插入单元格值
EN

回答 2

Stack Overflow用户

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问跳过代码以在单元格为空的位置插入单元格值EN

回答 2

Stack Overflow用户

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问跳过代码以在单元格为空的位置插入单元格值
EN