时间戳

首页 > 解决方案 > I can't put a selected cell in variable in vba

excel - I can't put a selected cell in variable in vba

问题描述

I'm new in VBA but I'll try to explain myself. I'm trying to make a variable from a selected cell and with it, elaborate a mail using cell.Offset but I dont know how to advance. This is my code:

    Sub sendmail()
Dim i, j As Integer
Dim pagina1 As Worksheet
Set pagina1 = ActiveWorkbook.Worksheets("Example1")
Dim OutApp As Object
Dim Correo As Object
Dim cell As Range
With Application
.EnableEvents = False
.ScreenUpdating = False
End With
On Error Resume Next
Set OutApp = GetObject("", "Outlook.Application")
Err.Clear
If OutApp Is Nothing Then Set OutApp = CreateObject("Outlook.Application")
OutApp.Visible = True
Set Correo = OutApp.CreateItem(0)

'THIS IS THE PLACE WHERE THE SENTENCE THAT I NEED, SHOULD BE

        email_ = cell.Value
       'subject_ = cell.Offset(0, 1).Value
        body_ = cell.Offset(0, 11).Value
        body1_ = cell.Offset(0, 6).Value
        cc_ = cell.Offset(0, 2).Value
        attach_ = cell.Offset(0, 4).Value
        destinatario_ = cell.Offset(0, 16).Value
        memofolio_ = cell.Offset(0, 17).Value
        Nmemofolio_ = cell.Offset(0, 18).Value
        Fechamemofolio_ = cell.Offset(0, 19).Value

'Crear el correo y mostrarlo
With Correo
    .To = email_
    .CC = cc_
    .Subject = "Status of the Project"
    .Body = "Infomo a usted que la iniciativa con nombre: " & body1_ & " fue enviada a " & destinatario_ & " via " & memofolio_ & " N°" & Nmemofolio_ & " con fecha " & Fechamemofolio_ & " para su revisión. Saluda Atentamente a usted, Unidad de Preinversión División de Planificación y Desarrollo"
    .Display
End With
With Application
.EnableEvents = True
.ScreenUpdating = True
End With

End Sub

If you can help me with it, I'll be eternally grateful

标签： excelvba

解决方案

您需要将单元格变量设置为一个范围，同时指定工作表

例如

Set cell = pagina1.cells(1,1)

或者

Set cell = pagina1.Range("A1")

我更喜欢上面的内容，因为它是具体且明确的。如果有选择单元格的特定操作，您也可以使用选择

Set cell = Selection

推荐阅读