Excel table to mail via Commandbutton.

TDNova

New Member
Joined
Apr 16, 2018
Messages
45
Hello, im trying to figure out how to make this code better. currently the code only takes one row,since its based on cell position.

Code:
Dim i As Integer, Mail_Object, Email_Subject, o As Variant
Dim TypeS As String
TypeS = ActiveSheet.ListObjects("Orderoversikt").ListColumns(1).Range.Column
 
    Set Mail_Object = CreateObject("Outlook.Application")
        With Mail_Object.CreateItem(o)
            .Subject = ActiveSheet.ListObjects("Orderoversikt")
            .To = "Mail"
            .cc = "frommail"
            .Body = "cell1: " & ActiveSheet.Range("r4").Value & Chr(13) & Chr(10) & "cell2: " & ActiveSheet.Range("S4").Value & Chr(13) & Chr(10) & "cell3: " & ActiveSheet.Range("t4").Value & Chr(13) & Chr(10) & "cell4:" & ActiveSheet.Range("u4").Value & Chr(13) & Chr(10) & "cell5: " & ActiveSheet.Range("v4").Value & Chr(13) & Chr(10) & "cell6: " & ActiveSheet.Range("w4").Value
            .Display
    End With
        Application.DisplayAlerts = False
Set Mail_Object = Nothing
End Sub
Is it possible to make a code that would put the whole table in the mail instead of just one row.
 

TDNova

New Member
Joined
Apr 16, 2018
Messages
45
For example, if it's based on string with all the information, if it find more than one row, add it aswell with same format.
because it should be a way to make it dynamic.
 

TDNova

New Member
Joined
Apr 16, 2018
Messages
45
is it possible with a code like this ?

Code:
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)Dim Targ As Range
Set Targ = Intersect(Target, Range("x7:x20"))
'Doubleclick in column P to trigger macro
If Not Targ Is Nothing Then
    SendEmail Target.Row
    Cancel = True       'undo the doubleclick effect if macro triggered
    Sub SendEmail(Row As Long)
Dim OutApp As Object
Dim OutMail As Object
Dim msg As String
On Error GoTo cleanup
msg = "Message: " & _
    Range("r" & Row) & " - " & Range("w" & Row) & ".  Thank you."
Set OutApp = CreateObject("Outlook.Application")
With Application
    .EnableEvents = False
    .ScreenUpdating = False
End With
Set OutMail = OutApp.CreateItem(0)
With OutMail
    .To = "mail"
    .cc = "tomail"
    .Subject = "order"
    .Body = msg
    .Display  'Or use Send
End With
Range("x" & Row).Value = "Closed"
Set OutMail = Nothing
cleanup:
Set OutApp = Nothing
With Application
    .EnableEvents = True
    .ScreenUpdating = True


End If
End Sub
 

TDNova

New Member
Joined
Apr 16, 2018
Messages
45
Managed to fix it, but if people got other approaches i would gladly take a look !
 

Forum statistics

Threads
1,082,507
Messages
5,365,978
Members
400,863
Latest member
RobynP51

Some videos you may like

This Week's Hot Topics

Top