当前位置: 动力学知识库 > 问答 > 编程问答 >

vba - Copy Other Cell to Add to Email with Excel

问题描述:

I have this code to send and email out based on a change in cell a, however i want to have contents of other cells in the email body to more specifically locate the issue for the end user. I could not figure out the best way to target the particular cells i wanted. Any help would be appreciated!

Private Sub Worksheet_Change(ByVal Target As Range)

If Not Intersect([A2:A1000], Target) Is Nothing Then

Set olApp = CreateObject("Outlook.application")

Set M = olApp.CreateItem(olMailItem)

Dim rngTo As Range

Dim rngBody As Range

Dim objOutlook As Object

Dim objMail As Object

Dim Issues As String

Dim Desc As String

Issues = Target.row

With ActiveSheet

'here is where i'm trying to get the contents of "F"

Desc = Range("F" & Issues)

Set rngBody = Intersect([A2:A1000], Target)

End With

rngBody.Copy

With M

.Subject = "Issue Tracker Has Changed"

.Body = "The Status of Your Issue" & Desc & " Has Changed to " & rngBody

.Recipients.Add "[email protected]"

.Send

End With

End If

End Sub

网友答案:

I solved this by taking the row value of Target and combing it with the column I needed the data from and returned it as a string in the body..

    Dim Issues As String
    Dim Desc As String
    Issues = Target.row

    Desc = Range("F" & Issues)


.Body = "The Status of Your Issue" & Desc & " Has Changed to " & rngBody.Value

by adding these lines i was able to accomplish the issue I had

分享给朋友:
您可能感兴趣的文章:
随机阅读: