Writing Values Of Cells Into Another Excel In VBA

Question

I have this code to send e-mail with attached via Outlook:

Sub AutoEmail()
    On Error GoTo Cancel

    Dim Resp As Integer
    Resp = MsgBox(prompt:=vbCr & "Yes = Review Email" & vbCr & "No = Immediately Send" & vbCr & "Cancel = Cancel" & vbCr, _
    Title:="Review email before sending?", _
    Buttons:=3 + 32)

    Select Case Resp

        'Yes was clicked, user wants to review email first
        Case Is = 6
            Dim myOutlook As Object
            Dim myMailItem As Object

            Set otlApp = CreateObject("Outlook.Application")
            Set otlNewMail = otlApp.CreateItem(olMailItem)
            FName = Application.ActiveWorkbook.FullName

            With otlNewMail
            .To = Cells(33, 10)
            .CC = Cells(1, 1)
            .Subject = Cells(23, 10) & ": " & Cells(21, 10)
            .Body = "Good morning" & vbCr & vbCr & "" & Cells(23, 10) & "."
            .Attachments.Add FName
            .Display

            End With


            Set otlNewMail = Nothing
            Set otlApp = Nothing
            Set otlAttach = Nothing
            Set otlMess = Nothing
            Set otlNSpace = Nothing


        'If no is clicked
        Case Is = 7
            Dim myOutlok As Object
            Dim myMailItm As Object

            Set otlApp = CreateObject("Outlook.Application")
            Set otlNewMail = otlApp.CreateItem(olMailItem)
            FName = ActiveWorkbook.Path & "\" & ActiveWorkbook.Name

            With otlNewMail
            .To = ""
            .CC = ""
            .Subject = ""
            .Body = "Good Morning," & vbCr & vbCr & " " & Format(Date, "MM/DD") & "."
            .Attachments.Add FName
            .Send
            '.Display
            'Application.Wait (Now + TimeValue("0:00:01"))
            'Application.SendKeys "%s"

            End With

            'otlApp.Quit

            Set otlNewMail = Nothing
            Set otlApp = Nothing
            Set otlAttach = Nothing
            Set otlMess = Nothing
            Set otlNSpace = Nothing
        'If Cancel is clicked
        Case Is = 2
Cancel:
            MsgBox prompt:="No Email has been sent.", _
            Title:="EMAIL CANCELLED", _
            Buttons:=64

    End Select

End Sub

However there is another thing that i want to add. I want to write some cells into another excel after sending e-mail via Outlook, lets say A2 to B15. The excel file which i want to write on is in C:\Users\Computername\Desktop\Savingdata.xlsx

Take a look at this one (possibly a duplicate): https://stackoverflow.com/questions/23132044/excel-save-data-into-different-workbook-vba — sɐunıɔןɐqɐp, Apr 25 '18 at 06:35

score 0 · Answer 1 · answered Apr 25 '18 at 06:51

0

Mert,

Try the following, add these two lines at the beginning of your code:

Dim wbThisWorkbook, wbTheOneToSaveTo As Workbook
Set wbThisWorkbook = Workbooks("TheNameOfYourCurrentWorkbook")

Then after your sending routine, add this:

Set wbTheOneToSaveTo = Workbooks.Open ("C:\Users\Computername\Desktop\Savingdata.xlsx")
wbThisWorkbook.Sheets("TheNameOfThe Worksheet").Range("A2").Copy
wbTheOneToSaveTo.Sheets("TheNameOfTheWorksheet").Range("B15").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False  'adjust parameters according to your needs
wbTheOneToSaveTo.Close True
wbThisWorkbook.Activate

Hope this helps!

answered Apr 25 '18 at 06:51

EarlyBird2

296
2
14

Hi Early, Thanks a lot! Could you please tell where exactly i need to put these codes? Sorry for being low level knowledge! :) – Mert Dogan Apr 25 '18 at 08:19
The way you have declared your `Workbook` means that only `wbTheOneToSaveTo` would be declared as a workbook. `wbThisWorkbook` would be declared as a `Variant` type. Also, it's poor coding practice to use things like `.Activate` and `.Select` in Excel VBA. As you already have the workbook object, you don't need to `Activate` the workbook – Zac Apr 25 '18 at 08:36
Add the two declarations just below the "On Error" statement and, as Zac critizised, do it separately, so two entries. Add the rest above the "Cancel:" mark, and you may leave out the Activate statement. – EarlyBird2 Apr 25 '18 at 08:44

Writing Values Of Cells Into Another Excel In VBA

1 Answers1